topic Comparing 2 similar files with the same fields but with different Data (Variance: This week vs Last week) in QlikView

Comparing 2 similar files with the same fields but with different Data (Variance: This week vs Last week)

Tue, 09 Mar 2010 16:05:27 GMT

Hi,

My first post. I just started using Qliksview yesterday and i dont have prior knowledge of script coding. so, I have two files, they have the same fields, just that the first file contains This Week's data and second file is Last Week's data. I am trying to do a simple variance of this weeks values vs Last week's values. An example of the 2 files are below e.g

First file:

Name	work ID	mon	tue	wed
Paul	a1234	2	3	3
John	a1235	3	4	4
Sam	a1236	4	4	4

Second file:

Name	work ID	mon	tue	wed
Paul	a1234	4	3	3
John	a1235	3	5	4
Sam	a1236	4	4	6

I am trying to come up with a pivot/table like this:

		This week			Last Week
Name	work ID	mon	tue	wed	mon	tue	wed
Paul	a1234	2	3	3	4	3	3
John	a1235	3	4	4	3	5	4
Sam	a1236	4	4	4	4	4	6

THe Name and Work ID fields are the unique identifiers.

After I loaded the 2 tables, only the first table gets read by Qlikview and the second one is not read at all. Please what is the fastest way to do this.

Thanks you all so much in advance as it is very urgent

Comparing 2 similar files with the same fields but with different Data (Variance: This week vs Last week)

gajapathy74 — Tue, 09 Mar 2010 16:49:51 GMT

Hi,

Introduce one more column (field) as Week in each table and update the value as "current" and "last" accordingly. This should resolve your problem.

Regards,

Comparing 2 similar files with the same fields but with different Data (Variance: This week vs Last week)

lironbaram — Tue, 09 Mar 2010 16:55:31 GMT

hi,

you are missing a field in the table week id or something

you can add it in the load script

but you need to know for each file to what week it belongs

if you can send me your script

i'll add it to it.

Comparing 2 similar files with the same fields but with different Data (Variance: This week vs Last week)

Tue, 09 Mar 2010 17:25:57 GMT

Thanks for your quick response:

This is the code below:

These are the files. I dont have fields that differentiates the data. Should I create a new field in the document before uploading them unto Qlikview.

This_Week:
LOAD [Program Id],
[Program Name],
[Client ID],
[System ID],
[Jan Total 2010],
[Feb Total 2010],
[Mar Total 2010],
[Apr Total 2010],
[May Total 2010],
[Jun Total 2010],
[Jul Total 2010],
[Aug Total 2010],
[Sep Total 2010],
[Oct Total 2010],
[Nov Total 2010],
[Dec Total 2010],
[Total Amount 2010],
[Jan Labor 2010],
[Feb Labor 2010],
[Mar Labor 2010],
[Apr Labor 2010],
[May Labor 2010],
[Jun Labor 2010],
[Jul Labor 2010],
[Aug Labor 2010],
[Sep Labor 2010],
[Oct Labor 2010],
[Nov Labor 2010],
[Dec Labor 2010],
[Total Labor Amount 2010]

FROM
This_Week.xls
(biff, embedded labels, table is [03082010$]);

Last_Week:
LOAD [Program Id],
[Program Name],
[Client ID],
[System ID],
[Jan Total 2010],
[Feb Total 2010],
[Mar Total 2010],
[Apr Total 2010],
[May Total 2010],
[Jun Total 2010],
[Jul Total 2010],
[Aug Total 2010],
[Sep Total 2010],
[Oct Total 2010],
[Nov Total 2010],
[Dec Total 2010],
[Total Amount 2010],
[Jan Labor 2010],
[Feb Labor 2010],
[Mar Labor 2010],
[Apr Labor 2010],
[May Labor 2010],
[Jun Labor 2010],
[Jul Labor 2010],
[Aug Labor 2010],
[Sep Labor 2010],
[Oct Labor 2010],
[Nov Labor 2010],
[Dec Labor 2010],
[Total Labor Amount 2010]

FROM
Last_week.xls
(biff, embedded labels, table is [03042010$]);

THanks again!!

Comparing 2 similar files with the same fields but with different Data (Variance: This week vs Last week)

gajapathy74 — Tue, 09 Mar 2010 18:36:15 GMT

Hi,

Find attached the document. Hope this is what you are expecting.

Regards,

Comparing 2 similar files with the same fields but with different Data (Variance: This week vs Last week)

Tue, 09 Mar 2010 22:14:10 GMT

THanks. But i cant open up your file because i am using the demo version. is it possible to paste the code you were trying to show me?

THank you in advance.

Comparing 2 similar files with the same fields but with different Data (Variance: This week vs Last week)

gajapathy74 — Wed, 10 Mar 2010 05:52:42 GMT

Hi,

Please find below the script.

Directory;
LOAD [Program Id],
[Program Name],
[Client ID],
[System ID],
[Jan Total 2010],
[Feb Total 2010],
[Mar Total 2010],
[Apr Total 2010],
[May Total 2010],
[Jun Total 2010],
[Jul Total 2010],
[Aug Total 2010],
[Sep Total 2010],
[Oct Total 2010],
[Nov Total 2010],
[Dec Total 2010],
[Total Amount 2010],
[Jan Labor 2010],
[Feb Labor 2010],
[Mar Labor 2010],
[Apr Labor 2010],
[May Labor 2010],
[Jun Labor 2010],
[Jul Labor 2010],
[Aug Labor 2010],
[Sep Labor 2010],
[Oct Labor 2010],
[Nov Labor 2010],
[Dec Labor 2010],
[Total Labor Amount 2010],
'ThisWeek' AS Week
FROM
This_Week.xls
(biff, embedded labels, table is Sheet1$);

Directory;
LOAD [Program Id],
[Program Name],
[Client ID],
[System ID],
[Jan Total 2010],
[Feb Total 2010],
[Mar Total 2010],
[Apr Total 2010],
[May Total 2010],
[Jun Total 2010],
[Jul Total 2010],
[Aug Total 2010],
[Sep Total 2010],
[Oct Total 2010],
[Nov Total 2010],
[Dec Total 2010],
[Total Amount 2010],
[Jan Labor 2010],
[Feb Labor 2010],
[Mar Labor 2010],
[Apr Labor 2010],
[May Labor 2010],
[Jun Labor 2010],
[Jul Labor 2010],
[Aug Labor 2010],
[Sep Labor 2010],
[Oct Labor 2010],
[Nov Labor 2010],
[Dec Labor 2010],
[Total Labor Amount 2010],
'LastWeek' AS Week
FROM
Last_week.xls
(biff, embedded labels, table is Sheet1$);

Regards,