topic Re: Applymap-aggregate by ID in QlikView

Applymap-aggregate by ID

Thu, 22 Oct 2015 00:58:30 GMT

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

Re: Applymap-aggregate by ID

Thu, 22 Oct 2015 01:00:27 GMT

Add 2, b, b row also

Re: Applymap-aggregate by ID

sunny_talwar — Thu, 22 Oct 2015 01:09:05 GMT

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;

Re: Applymap-aggregate by ID

Thu, 22 Oct 2015 01:23:13 GMT

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

Re: Applymap-aggregate by ID

Fri, 23 Oct 2015 01:50:11 GMT

How to do the same in the front end?

Re: Applymap-aggregate by ID

MK_QSL — Fri, 23 Oct 2015 06:20:12 GMT

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

Re: Applymap-aggregate by ID

maxgro — Fri, 23 Oct 2015 11:43:01 GMT

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

];

Re: Applymap-aggregate by ID

Anonymous — Fri, 23 Oct 2015 12:02:00 GMT

Hey!

PFA

Re: Applymap-aggregate by ID

Sat, 24 Oct 2015 01:53:20 GMT

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.

Re: Applymap-aggregate by ID

Sat, 31 Oct 2015 03:20:37 GMT

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.