Summary in load equivalent of Max(case .......

[mutangadura]

I have data in a table where I have ID, Date, Option, etc.....

there are multiple records in this table based on ID as an ID can make a transaction on multiple dates and thus one row per record. An ID can take Option eg A multiple times till success all with differing outcomes for each trial. They can do the same for Option B, C ..... you get the point.

I am looking to load this into a single table where I only have the success or fail for each ID and Option.

so far I've used an IF statement in the load giving me the following

ID	A	B	C
kdkdkd	Y	-	-
kdkdkd	-	Y	-
kdkdkd	-	-	Y

this is based on If(Option = A and outcome = MATCH(2,3,4,5),Y,NULL()) as A

the reason for the above is because success Y can be defined by say 2,3,4 or 5

What I am looking for however is to get a load where I can have just the following:

ID	A	B	C
kdkdkd	Y	Y	Y

[Gysbert_Wassenaar]

Something like this maybe:

LOAD

     ID,

     MaxString(If(A >=2 AND A <=5,'Y') as A,

     ...same for B and C ...

FROM

     ...somewhere

GROUP BY

     ID

;

[mutangadura]

Hi, thanks but I get an invalid script error.  To clarify A can be defined by any value not necessary an integer but I can use match that's fine. It's when it gets to MaxString where the issue is.

[Gysbert_Wassenaar]

Perhaps this then: If(Max(Match(A,'2','3','4','5'),'Y') as A

[mutangadura]

Thanks appreciate all the help

[mutangadura]

Thanks that worked like a charm