Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello ,
My name is Jaya, I am very new to qlikview i trying to fulfill these below requirement and couldn't able to achieve it. So kindly some look into this and any help would be greatly appreciated.
I have a table with 5 fields (Gen ID, Q ID, CODE, Amount, and Step) . I would need to added 3 new fields to the table with names (Amount Increase FLAG, Amount Decrease FLAG, CODE ADDED )
CODE ADDED - If there is any CODE with its GenID and Q ID present in Step ‘TWO’ should be fagged as ‘1’ (Note: If Same combination of the CODE ‘s GenID and Q ID is present in Setp ‘ONE’ for any of the records in the table, then we don’t flag that record. Please see the screen shot of required table.)
Amount Increase FLAG – Need to create a flag, where the record with same Gen D,Q ID and CODE present in Step ‘ONE’ and the same combination of record present in Step ‘TWO’ – then if Amount in Step ‘ONE’ is greater than Step’TWO’ – then create a flag at Step ’ONE’ as ‘1’
Amount Decrease FLAG – Need to create a flag, where the record with same Gen D,Q ID and CODE present in Step ‘ONE’ and the same combination of record present in Step ‘TWO’ – then if Amount in Step ‘ONE’ is less than Step’TWO’ – then create a flag at Step ’ONE’ as ‘1’
Thanks in Advance,
Jaya
Try two LEFT JOINs and a reload into a new Resident table. Let's call your original data set "Table":
LEFT JOIN(Table)
LOAD 'TWO' AS Step, Gen ID, [Q ID], CODE, 1 AS [INVERSE CODE ADDED]
RESIDENT Table
WHERE Step = 'ONE';
LEFT JOIN(Table)
LOAD 'ONE' AS Step, Gen ID, [Q ID], CODE, Amount AS Step2Amount
RESIDENT Table
WHERE Step = 'TWO';
The final piece of code doesn't obey your exaplanation, as it defined Increase flag = 1 as meaning that TWO < ONE? I reversed this to match your example output table, not the explanation:
FinalTable:
NOCONCATENATE
LOAD Gen ID, [Q ID], CODE, Step,
IF ((Step = 'ONE') AND (Amount < Step2Amount)), 1) AS [Amount Increase FLAG],
IF ((Step = 'ONE') AND (Amount > Step2Amount)), 1) AS [Amount Decrease FLAG],,
IF ((Step = 'TWO') AND IsNull([INVERSE CODE ADDED]), 1) AS [CODE ADDED]
RESIDENT Table;
DROP Table Table;
RENAME Table FinalTable TO Table;
Best,
Peter
Try two LEFT JOINs and a reload into a new Resident table. Let's call your original data set "Table":
LEFT JOIN(Table)
LOAD 'TWO' AS Step, Gen ID, [Q ID], CODE, 1 AS [INVERSE CODE ADDED]
RESIDENT Table
WHERE Step = 'ONE';
LEFT JOIN(Table)
LOAD 'ONE' AS Step, Gen ID, [Q ID], CODE, Amount AS Step2Amount
RESIDENT Table
WHERE Step = 'TWO';
The final piece of code doesn't obey your exaplanation, as it defined Increase flag = 1 as meaning that TWO < ONE? I reversed this to match your example output table, not the explanation:
FinalTable:
NOCONCATENATE
LOAD Gen ID, [Q ID], CODE, Step,
IF ((Step = 'ONE') AND (Amount < Step2Amount)), 1) AS [Amount Increase FLAG],
IF ((Step = 'ONE') AND (Amount > Step2Amount)), 1) AS [Amount Decrease FLAG],,
IF ((Step = 'TWO') AND IsNull([INVERSE CODE ADDED]), 1) AS [CODE ADDED]
RESIDENT Table;
DROP Table Table;
RENAME Table FinalTable TO Table;
Best,
Peter