Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

m_perreault
Contributor II

If Count > 1 then various in Load Script

Hi All,

I have the below table

IDSubIDStateTypePriority
11AMASmallHigh
11BCTHigh
22AVAMediumLow
33AVTSmallHigh
33BNHSmallMedium
33CMESmallLow

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

IDStateTypePriority
1VariousSmallHigh
2VAMediumLow
3VariousSmallVarious

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

IDStateTypePriority
1VariousVariousVarious
1Various-Various
2LowMediumVarious
3VariousVariousVarious

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

Thanks,

Mark

1 Solution

Accepted Solutions

Re: If Count > 1 then various in Load Script

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
1VariousSMALLHIGH
2VAMEDIUMLOW
3VariousSMALLVarious
1 Reply

Re: If Count > 1 then various in Load Script

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
1VariousSMALLHIGH
2VAMEDIUMLOW
3VariousSMALLVarious