New Fields to the existing table in edit script.

[#1]

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

[Peter_Cammaert · Accepted solution]

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