If Count > 1 then various in Load Script

[m_perreault]

Hi All,

I have the below table

ID	SubID	State	Type	Priority
1	1A	MA	Small	High
1	1B	CT		High
2	2A	VA	Medium	Low
3	3A	VT	Small	High
3	3B	NH	Small	Medium
3	3C	ME	Small	Low

In my final table I want to roll up my SubIDs to one line item for 'ID' with the logic for state and type

if(Count(State)>1,'Various',State) as State

if(Count(Type)>1,'Various',Type) as Type

leaving me with a final table of

ID	State	Type	Priority
1	Various	Small	High
2	VA	Medium	Low
3	Various	Small	Various

Currently I am doing this by

set NullInterpret = '';

Table1:

Load * Inline

[

ID, SubId, State, Type, Priority

1,1A,MA,SMALL,HIGH

1,1B,CT,,HIGH

2,2A,VA,MEDIUM,LOW

3,3A,CT,SMALL,HIGH

3,3B,NH,SMALL,MEDIUM

3,3C,ME,SMALL,LOW

]

;

Left Join(Table1)

Load Distinct

     ID,

     Count(State) as StateCount,

     Count(Type) as TypeCount,

     Count(Priority) as PriorityCount

Resident Table1

Group by ID;

NoConcatenate

Table2:

Load Distinct

     ID,

     If(StateCount>1,'Various',State) as State,

     If(TypeCount>1,'Various',Type) as Type,

     If(PriorityCount>1,'Various',Priority) as Priority

Resident Table1;

Drop table Table1;

Exit script

My output table is this however

ID	State	Type	Priority
1	Various	Various	Various
1	Various	-	Various
2	Low	Medium	Various
3	Various	Various	Various

Does anyone have any suggestions on how to accomplish what I am looking for?

Thanks,

Mark

[Nicole-Smith · Accepted solution]

Load script like this:

SET NullInterpret='';

Temp:
Load * Inline [
 ID, SubId, State, Type, Priority
 1,1A,MA,SMALL,HIGH
 1,1B,CT,,HIGH
 2,2A,VA,MEDIUM,LOW
 3,3A,CT,SMALL,HIGH
 3,3B,NH,SMALL,MEDIUM
 3,3C,ME,SMALL,LOW
];

Final:
LOAD ID,
 IF(COUNT(DISTINCT State) > 1, 'Various', MINSTRING(State)) AS State,
 IF(COUNT(DISTINCT Type) > 1, 'Various', MINSTRING(Type)) AS Type,
 IF(COUNT(DISTINCT Priority) > 1, 'Various', MINSTRING(Priority)) AS Priority
RESIDENT Temp
GROUP BY ID;
DROP TABLE Temp;

Will return an output table like this:

ID	State	Type	Priority
1	Various	SMALL	HIGH
2	VA	MEDIUM	LOW
3	Various	SMALL	Various