Current Vs Previous Values

[agnie_john]

Hi,

I am creating Qliksense application.

The user wants to compare current and previous month values.

I have derived a table which contains each months data.

i would like to calculate the corresponding previous month data in the same table.

This is my input table. 

Key	PreviousKey	Item	Amount
COL1_COL2_1_Item1	COL1_COL2_12_Item1	Item1	1200
COL1_COL2_1_Item2	COL1_COL2_12_Item1	Item2	1100
COL1_COL2_2_Item1	COL1_COL2_1_Item1	Item1	2000
COL1_COL2_2_Item2	COL1_COL2_1_Item1	Item2	2100
COL1_COL2_3_Item1	COL1_COL2_1_Item1	Item1	3000
COL1_COL2_3_Item2	COL1_COL2_1_Item1	Item2	3200

I want to derive the output table like below

Key	Item	Amount	Previous Amount
COL1_COL2_1_Item1	Item1	1200	-
COL1_COL2_1_Item2	Item2	1100	-
COL1_COL2_2_Item1	Item1	2000	1200
COL1_COL2_2_Item2	Item2	2100	1100
COL1_COL2_3_Item1	Item1	3000	2000
COL1_COL2_3_Item2	Item2	3200	2100

My key column is the composite key(concatenating multiple columns to form a key).

Please give me your valuable suggestion to achieve this.

Thanks,

John

[mato32188]

Hi John,

in load script editor follow these steps:

tmp:

LOAD

* ,

Subfield(Key,'_',3) as Month

from YOUR INPUT TABLE;

noconcatenate

Output:

LOAD

*, 

Peek(Amount,-2) as "Previous Amount"

resident tmp

order by Month,Item;

drop table tmp;

BR

Martin

[JordyWegman]

Hi Angie,

You should Left Join the table with the previous key and previous amounts to the new key. This way you will have your result. 

Can you show some information from the key and previous key. Like:

Key	PreviousKey	Item	Amount
COL1_COL2_1_Item1	COL1_COL2_12_Item1	Item1	1200

To this key, 1200 is the amount, but what is the amount of the previous key. What corresponds to this and where can this information be found?

Jordy

Climber