Applymap-aggregate by ID

[#1]

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..

[#2]

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;

[#4]

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...

[#5]

How to do the same in the front end?

[MK_QSL]

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]

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]

Hey!

PFA

[#9]

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.

[#10]

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.