topic Re: Need help in scripting in QlikView

Need help in scripting

kunkumnaveen — Thu, 05 Dec 2019 08:43:18 GMT

Hello All,

Need a help in creating a Flag column at script with values 1,0.

If for a combination of MC,VC,PC if VF is >= 01-09-2019(DD-MM-YYYY),

then flag 1 should be assign else 0.

For example the first row in the below table should get flag 0 because valid from is not >= 01-09-2019

Output required

Please find the sample data attachment and required output pic

Re: Need help in scripting

zhadrakas — Thu, 05 Dec 2019 10:07:37 GMT

that should work:

SET DateFormat='DD-MM-YYYY'; SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]'; LET vValidFrom = date('01-09-2019'); SOURCE: Load *, if(date(VF) >= '$(vValidFrom)', 1,0) as Flag ; LOAD MC, VC, PC, max(VF) as VF FROM [C:\tmp\TEST.xlsx] (ooxml, embedded labels, table is Sheet1) GROUP BY MC, VC, PC; drop field VF from SOURCE; left join load MC, VC, PC, VF FROM [C:\tmp\TEST.xlsx] (ooxml, embedded labels, table is Sheet1);