Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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...
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..
Is this the result you want (4th column)?
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