Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Applymap-aggregate by ID

ID, Type, Result

1,  a,     a

1,  b,    a

1, c,     a

2,  -,     b

2, c,  b

3, d,  d

3, e, e

If there is "a" value with respect to ID in column "Type" , all values of column "result" should show value "a" corresponding to that column "ID".  If there is no " a" look for "b" and the  result column should show "b" for that ID, rest should remain same as "Type" in "Result".... In short: calculate the  result column using ID and Type..

9 Replies
Not applicable
Author

Add 2, b, b row also

sunny_talwar

May be this:

Table:

LOAD * Inline [

ID, Type

1,  a

1,  b

1, c

2,  b

2, -

2, c

3, d

3, e

];

Temp:

LOAD ID as ID1,

  Type as Result

Resident Table

Where Type = 'a';

Concatenate(Temp)

LOAD ID as ID1,

  Type as Result

Resident Table

Where Type = 'b' and not Exists(ID1, ID);

Join(Table)

LOAD ID1 as ID,

  Result

Resident Temp;

DROP Table Temp;

FinalTable:

NoConcatenate

LOAD ID,

  Type,

  If(Len(Trim(Result)) = 0, Type, Result) as Result

Resident Table;

DROP Table Table;


Capture.PNG

Not applicable
Author

Cool ...that's was real quick....but what if I have so many distinct values in column "Type" !!! Cannot use where conditions  for all...it becomes to lenghty...

Not applicable
Author

How to do the same in the front end?

MK_QSL
MVP
MVP

Just want to understand your question first. You said you want to know the first occurrence of a. If a is not there then go for b. If you are only interested in a and b there should be no problem if many distinct Type are present. Let me know if I am thinking in wrong direction or may be dont understood your question. We are ready to help further..

maxgro
MVP
MVP

If I understand, when you have many values in Type (a, b, x, y) you can


Left Join (Table)

load ID, MinString(Type) as Result

Resident Table

Where Match(Type, 'a', 'b', 'x', 'y')

group by ID;

FinalTable:

NoConcatenate load

ID, Type, if(isnull(Result), Type, Result) as Result

Resident Table;

DROP Table Table;

table is

Table:

LOAD * Inline [

ID, Type

1, a

1, b

1, c

2, b

2, c

3, d

3, e

4, a

4, f

5, b

5, g

7, a

7, x

8, x

8, e

9, y

9, d

];

Anonymous
Not applicable
Author

Hey!

PFA

Not applicable
Author

Ok, l will try: I want to keep distinct  type values( number in 1000's) in hierarchy in an excel file. Note: This order can change over period. Use the excel sheet for above analysis.

Not applicable
Author

Is it possible in qlikview? I want to keep distinct  type values (number in 1000's) in hierarchy in an excel file.

Note: This order can change over period. Use the excel sheet for above analysis.