topic Re: Re: Copy Old Table in Load Script... in QlikView

Copy Old Table in Load Script...

Fri, 28 Feb 2014 00:21:19 GMT

I have a QVW file that loads CSV's and outputs QVD files. Before the QVD is moved to our live server I have a team that is supposed to analyze the data. What I would like to do is give them the ability to see before and after. My thought is on refresh I'd copy the previously load table into a "previous_week" table and then drop the current table, then reload the new CSV file. So something like this (in non-Qlikview syntax)...

DROP TABLE previous_week;

COPY TABLE current_week TO previous_week;

DROP TABLE current_week;

current_week;

Load * from new_week_data.csv;

Any ideas on how to make this work?

Re: Copy Old Table in Load Script...

Fri, 28 Feb 2014 00:52:22 GMT

Hi Michael,

You can do something like this. Keep two copies in QVD for your table with both data states. One Previous_week.QVD and one Current_week.QVD

1) Override Previuos_week with the Current_week data.

2) Override existing Current_week.qvd with the load from CSV data updated.

3) Load both QVD in data model and mantein unlinked two tables.

-----

Previuos_week:

Load * FROM Current_week;

STORE Previuos_week INTO Previuos_week.qvd;

drop table Previuos_week;

Current_week:

Load * FROM new_week_data.csv;

STORE Current_week INTO Current_week.qvd;

drop table Current_week;

And then load both QVD.

Current_week:

Load * FROM Current_week.qvd;

Qualify *;

Previous_week:

Load * FROM Previous_week.qvd;

Unqualify *;

Qualify and Unqualify functions are for add as name of each field the name of your table, it allow mantein the tables not linked.

EDIT: You can also maintain both tables linked by the primary key and load twice this primary key with another name in each table to can test data more directly.

For example:

Current_week:

Load *, PrimaryKey as Current_week.PrimaryKey FROM Current_week.qvd;

Qualify *;

Unqualify PrimaryKey;

Previous_week:

Load *, PrimaryKey as Previous_week.PrimaryKey FROM Previous_week.qvd;

Unqualify *;

Tell me if your have any question.

Regards.

Re: Re: Copy Old Table in Load Script...

Fri, 28 Feb 2014 16:58:02 GMT

I'm getting a table does not exist error on #1 during the load. I'm attached my entire load script here. The only other thing I can assume you mean is to keep the actual QVD files around and load them. Any insights would be great!

Re: Copy Old Table in Load Script...

Fri, 28 Feb 2014 17:28:11 GMT

Hey Michael,

If I understood correctly you want to compare previous qvd with the current qvd data before the previous one gets over-written by the current one.

In that case you can just add timestamp to the qvds like this.

let vDate= date(today(),'YYYYMMDD');

Store Test into Test_$(vDate).qvd(qvd);

This will suffix a timestamp to the qvd and will clearly tell you when the file was run.

To read the latest one do the same.

let vDate= date(today(),'YYYYMMDD');

Load *

From Test_$(vDate).qvd(qvd);

Hope it helps

Thanks

Re: Copy Old Table in Load Script...

Sat, 01 Mar 2014 01:51:21 GMT

Hi Michael,

Sorry for the delay in my answer but I was hard at work.

The Ajay suggestion is a good idea for avoid loads not necessaries, but I explain that I wrote previously, perhaps is more easy, I dont know.

First of all, think that create two QVDs files, one for new information and one for previous information.

So, if ever you only have a CSV file with the new information, at the first time you should create the both QVDs with something like this:

DATA:

Load * FROM new_week_data.csv;

STORE DATA INTO Previuos_week.qvd;

STORE DATA INTO Current_week.qvd;

drop table DATA;

Else, if you have two CSV files, one with new information and other with previous information, at the first time you should create the both QVDs with something like this:

Previuos_week:

Load * FROM previous_week_data.csv;

STORE Previuos_week INTO Previuos_week.qvd;

drop table Previuos_week;

Current_week:

Load * FROM new_week_data.csv;

STORE Current_week INTO Current_week.qvd;

drop table Current_week;

Then, for the next reloads, you can do something like this:

Previuos_week:

Load * FROM Current_week;

STORE Previuos_week INTO Previuos_week.qvd;

drop table Previuos_week;

Current_week:

Load * FROM new_week_data.csv;

STORE Current_week INTO Current_week.qvd;

drop table Current_week;

At this point you have the two QVDs file finished, one with the previous information, and other with new information.

So we load these QVD to compare:

Current_week:

Load *, PrimaryKey as Current_week.PrimaryKey FROM Current_week.qvd;

Qualify *;

Unqualify PrimaryKey;

Previous_week:

Load *, PrimaryKey as Previous_week.PrimaryKey FROM Previous_week.qvd;

Unqualify *;

If you want you can post your qvw and CSVs and I'll help you with pleasure.

I hope I have helped and with the begin of my post you will can solve your issue.

Regards.