topic Re: Setup Current and Previous period Amount at same row in QlikView

Setup Current and Previous period Amount at same row

Wed, 10 Apr 2013 22:17:33 GMT

Hi QlikView eprts,

I have source table with columns, Date, Account, Amount. Want to transform that to following format of Date, Account, Amount, PY AMT (for further calculations).

Tried to read source table in two different table and then those two resident table into a single table to get PY AMT, using Join and Concatenate, but didn't get expected results.

Appreciate any insights on the approach to do this.

Source Data

TDATE	ACCOUNT	AMOUNT
1/1/2012	A CONSUMER INVESTMENT	14
1/1/2012	B CONSUMER INVESTMENT	157
1/1/2012	C CONSUMER INVESTMENT	351
1/1/2012	D CONSUMER INVESTMENT	25
1/1/2012	E CONSUMER INVESTMENT	13
1/1/2012	F CONSUMER INVESTMENT	28
1/1/2012	G CONSUMER INVESTMENT	49
1/1/2012	H CONSUMER INVESTMENT	0
1/1/2011	A CONSUMER INVESTMENT	7
1/1/2011	B CONSUMER INVESTMENT	78.5
1/1/2011	C CONSUMER INVESTMENT	175.5
1/1/2011	D CONSUMER INVESTMENT	12.5
1/1/2011	E CONSUMER INVESTMENT	6.5
1/1/2011	F CONSUMER INVESTMENT	14
1/1/2011	G CONSUMER INVESTMENT	24.5
1/1/2011	H CONSUMER INVESTMENT	0

Expected Format

TDATE	ACCOUNT	AMOUNT	PY AMT
1/1/2012	A CONSUMER INVESTMENT	14	7
1/1/2012	B CONSUMER INVESTMENT	157	78.5
1/1/2012	C CONSUMER INVESTMENT	351	175.5
1/1/2012	D CONSUMER INVESTMENT	25	12.5
1/1/2012	E CONSUMER INVESTMENT	13	6.5
1/1/2012	F CONSUMER INVESTMENT	28	14
1/1/2012	G CONSUMER INVESTMENT	49	24.5
1/1/2012	H CONSUMER INVESTMENT	0	0

Thanks,

Abhay

Re: Setup Current and Previous period Amount at same row

Thu, 11 Apr 2013 08:31:45 GMT

amhatre1

try this my example script below

maybe it helps for you

LOAD DATE,

NAME,

AMOUNT

FROM

(SAMPLE1);

Concatenate

LOAD AddYears(DATE,1) as DATE,

NAME,

AMOUNT as PY.AMOUNT

FROM

(SAMPLE2);

regard

GERNAN

Re: Setup Current and Previous period Amount at same row

Thu, 11 Apr 2013 11:28:58 GMT

try this (see attached example)

Re: Setup Current and Previous period Amount at same row

MarcoARaymundo — Thu, 11 Apr 2013 14:49:40 GMT

Hi!

I'm not sure I understand what you want, but follows qvw for example.

Re: Setup Current and Previous period Amount at same row

Thu, 11 Apr 2013 16:04:56 GMT

Hi,

Can you explain where py amount comes from or how it's calculated?

Thanks,

Matt

Re: Setup Current and Previous period Amount at same row

MarcoARaymundo — Thu, 11 Apr 2013 16:30:04 GMT

Matt, to summarize amount by account, just set the account as a dimension and amount as expression (Sum). like to pivot table in excel.

I hope have helped.

Re: Setup Current and Previous period Amount at same row

Thu, 11 Apr 2013 17:20:11 GMT

HI ,

Try the below code .

Tab1:

LOAD ACCOUNT , max(TDATE) as TDATE , FirstValue(AMOUNT) as AMOUNT

From SourceData Group by ACCOUNT Order by ACCOUNT,TDATE ;

Left Join

LOAD ACCOUNT ,[PY AMT];

LOAD ACCOUNT , min(TDATE) as TDATE , FirstValue(AMOUNT) as [PY AMT]

From SourceData Group by ACCOUNT Order by ACCOUNT,TDATE desc;

Please let me know if there is anything .

Re: Setup Current and Previous period Amount at same row

MarcoARaymundo — Thu, 11 Apr 2013 18:49:16 GMT

Matt, In your code to use order by ACCOUNT, TDATE, dont work, because Group only has the field ACCOUNT, in the left join the first load missing from sourcetable.

Sorry! But, i do not understand what you want to do, help me to help you.

you want the max and min value of the amount field per account, that's it?

If yes, use the qlikview functions, is easiest!

By reducing the load (GROUP BY), your data information is limited to a particular situation and restricts reuse.

Re: Setup Current and Previous period Amount at same row

Thu, 11 Apr 2013 19:10:53 GMT

Hi,

It's not me that posted the question, I was asking him to define PY Amount in hopes of being able to create a chart with his requirements using set analyis.

It's Abhay that wrote the post.

Best,

Matt

Re: Setup Current and Previous period Amount at same row

Thu, 11 Apr 2013 19:15:28 GMT

The examples that zohaibirshad provided, gives the exact result I wanted (Thanks zohaibirshad), i.e. for the same Account have the previous and current year amounts/quantity value available at same row to do further calculation and then present it in QlikView Chart.

However, evaluating that sample further, since in my actual data, Key is complex than single ACCOUNT field(actually its combination of mutliple fields) and there are multiple amounts and quantities fields to compare for previous and current year.

Other concenrn is how the multiple joins in his script would fare for large data.

Will share the findings with the community soon.

Thanks,

Abhay

Re: Setup Current and Previous period Amount at same row

MarcoARaymundo — Thu, 11 Apr 2013 19:30:15 GMT

Sorry