How to do this: if any of Item ID's Action IDs = 100, mark that Item ID as "Yes", otherwise mark Item ID as "No".

[pgkrsk]

Hi,

Sorry for the confusing title, I had some trouble titling this. So what I have is bunch of Item IDs and each value one or multiple Action IDs. I wanna achieve the following: if any of Item ID's Action IDs = 100, mark that Item ID as "Yes", if an Item ID does not have any Action IDs that are 100, mark them as "No". I think I could use a flag for this but im unsure how to do it.

Thank you in advance!

[stevejoyce]

I'll assume the 2 fields exist in the same table and can be done in the load script:

//Get all item ids with action id of 100

Left join (<yourtable>):

  load distinct

  ItemID

,1 as tempFlag100

  resident <yourtable>

  where ActionID = 100

;

//update yourtable with final field flag

rename table <yourtable> to <tempyourtable>;

<yourtable>:

load *

,if(tempFlag100 = 1, 'Yes', 'No') as ItemID_Has100

resident <tempyourtable>;

drop table <tempyourtable>;

drop field tempFlag100;

[pgkrsk]

Thank you, that works. But When I create a Table Box with ItemID and ItemID_Has100, if an ItemID has for example one ActionID = 100 and another ActionID = 178, it will show the same ItemID twice with "Yes" when ActionID = 100 and "No" Where its ActionID = 178. I would want to make a table box where each ItemID appear only once and if it has one or more ActionIDs that are 100 it would say "Yes" and otherwise "No", not both Yes and No at the same time if that makes sense.

[stevejoyce · Accepted solution]

Can you send me your load script?  I understand what your issue and that shouldn't have happened.  You can try to exit script after the join and make sure each ItemID either has 1 or null.  And specifically the items that have a 100 actionID and non 100 action ID are all appropriately flagged. 

I did a quick test and this correctly shows ItemID =1  with only Yes, even though it has 100 and <> 100 actionIDs.

TBL1:
load * inline [
ItemID, ActionID
1, 100
1, 101
2, 100
3, 101

]
;

Left keep (TBL1)

load distinct

ItemID

,1 as tempFlag100

resident TBL1

where ActionID = 100

;

//update yourtable with final field flag

rename table TBL1 to tempTBL1;

TBL1:

load *

,if(tempFlag100 = 1, 'Yes', 'No') as ItemID_Has100

resident tempTBL1;

drop table tempTBL1;

drop field tempFlag100;