Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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 |