topic two group by in front end in QlikView

two group by in front end

Thu, 11 Dec 2014 12:01:19 GMT

Hello,

I am trying to get an expression working in the front end , which if calculated in the script has 2 group bys:

the expression is something like:

[Table 1]:

Load [key1], min(Item_key) as [min key]

resident [ABC]

GROUP BY [key1]

[Table 2]:

load min([min key]) as [min key]

resident [Table 1]

group by [key2]

when I use the field [min key] in an expression in a pivot table it works perfect..

However, when I use :

AGGR(MIN(AGGR(MIN(item_key), [key1])), [key2])) instead of [min key]

, it errors out....

but, when I display it in a separate pivot table against [key2], it works fine...

any clue what could be wrong...?

Many thanks.......

Re: two group by in front end

yduval75 — Thu, 11 Dec 2014 16:06:58 GMT

Try it

MIN(AGGR(MIN(item_key), [key1], [key2]))

Re: two group by in front end

Thu, 11 Dec 2014 17:05:57 GMT

DOESN'T WORK

Re: two group by in front end

Fri, 12 Dec 2014 06:14:30 GMT

My data is SOMEWHAT like this:

Key2 Key1 Date_Key

A 199 200101

A 199 200102

A 199 200903

A 232 200409

B 523 200807

B 677 200111

B 677 200402

C 73 200509

C 73 200709

C 74 200306

Now, I want this as my calculated dimension:

where for A : 1 represents 200101

2 represents 200102

3 represents 200409

4 represents 200903

B: 1 represents 200111

2 represents 200402

3 represents 200807

C: 1 represents 200306

2 represents 200509

3 represents 200709

In my expressions I have a measure for 2 different 'key2' keys, so I have two lines in the chart...

x-axis has 1, 2, 3, 4...... and so on... which represents different date_keys for different key2s and

y-axis has the corresponding measure values....

I hope you get the problem.. Please ask if I haven't been very clear in explaining something ..

Is there a way to achieve this? any help would be highly appreciated..

Re: two group by in front end

MK_QSL — Fri, 12 Dec 2014 08:23:29 GMT

Would like to help you but unable to understand your explanation..

Re: two group by in front end

Fri, 12 Dec 2014 08:35:11 GMT

Thanks Manish... which part of it would you like me to elaborate?

Re: two group by in front end

MK_QSL — Fri, 12 Dec 2014 09:53:45 GMT

Please explain your required result in brief. I am unable to understand what you explain above

Re: two group by in front end

Fri, 12 Dec 2014 10:03:45 GMT

what I want is datekeys on the dimension.. but since they vary for different items.. I want them to be relative as 1, 2, 3 and so on... where 1 represents the first datekey for an item in ascending order, 2 the second one and so on... corresponding to these datekeys we have a measure.. so I want these measures in my line chart ... and as I want these displayed for two items, I have two lines in my line chart...