Setup Current and Previous period Amount at same row

[#1]

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

[#2]

hi

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

[#3]

try this (see attached example)

[MarcoARaymundo]

Hi!

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

[#5]

Hi,

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

Thanks,

Matt

[MarcoARaymundo]

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.

[#7]

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 .

[MarcoARaymundo]

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.

[#9]

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

[#10]

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