topic Re: Scripting + How to group expiry status - if there is one sub record is Expired, consider the Record as Expired. in QlikView

Scripting + How to group expiry status - if there is one sub record is Expired, consider the Record as Expired.

jim_chan — Sat, 21 Jan 2017 11:55:52 GMT

Hi guys,

I have this scernario.

i have such table as example. I have 2 groups - 1 is consider Expired Record 1 is Not Expired Record.

Each RecordID has more than 1 ActionID.

If there is 1 recordID has 1 actionID which is Expired, that RECORDID will be considered as EXPIRED.

RecordID	ActionID	Expiry_Status
12345	123	Expired
12345	456	Not Expired
12345	789	Not Expired

I dunno how to do from loading point of view.

or if is from front end scripting point of view.

Re: Scripting + How to group expiry status - if there is one sub record is Expired, consider the Record as Expired.

rubenmarin — Sat, 21 Jan 2017 13:12:52 GMT

Hi Jim, you can create a map with expired records and use applymap, the expired recordIds will get the status from the map, those wich are not in the map will keep their original status:

Map_Status:

mapping LOAD RecordID, Expiry_Status

From/Resident... where Expiry_Status='Expired';

Final:

LOAD RecordID, ActionID, Expiry_Status, ApplyMap('Map_Status', RecordID, Expiry_Status) as RecordID_Expiry_Status

From/Resident...

Re: Scripting + How to group expiry status - if there is one sub record is Expired, consider the Record as Expired.

jim_chan — Sat, 21 Jan 2017 14:17:34 GMT

But my scenario is not like this. Do u mean I have to create another table to map?

I have a table that full of records ID and action ID. But 1 record ID can have more than 1 action ID.

so such recordID when it has 1 action ID with EXPIRY status, this record ID will be counted as an Expired record..

Re: Scripting + How to group expiry status - if there is one sub record is Expired, consider the Record as Expired.

maxgro — Sat, 21 Jan 2017 16:08:16 GMT

Is this the result you want (4th column)?

Re: Scripting + How to group expiry status - if there is one sub record is Expired, consider the Record as Expired.

rubenmarin — Sat, 21 Jan 2017 18:03:44 GMT

Hi Jim, I'm not sure to understand the problem... mapping tables are deleted when the script ends, they are used only to assign values while loading. It was an example than using a map you can check if an RecordID is expired and assign the 'Expired' value to all the records with the same RecordID.

I set the value in another field to keep the original original status but it can be in the same field

It can also be:

Map_Status:

mapping LOAD Distinct RecordID, 1 as Expired

From/Resident... where Expiry_Status='Expired';

Final:

LOAD *, If(ApplyMap('Map_Status', RecordID), 'Expired', Expiry_Status) as RecordID_Expiry_Status

From/Resident...

It has the backwards of having to load the distinct record ids in the map to check the expired

Another option, if the only values are Expired and Not Expired, and you can load sorted by RecordID and Expiry_Status, doing the checks with peek():

LOAD ...

If(Peek(RecordID)=RecordID and Peek(Expiry_Status)='Expired', 'Expired', Expiry_Status) as Expiry_Status