topic Re: Problem with aggr max in straight table in QlikView

Problem with aggr max in straight table

Wed, 23 Sep 2015 07:51:36 GMT

Hello dear community,

I'm stuck on a silly problem.

I have the following table:

In a straight table I wish to display only the lines which confirm to the following rule:

Max of sequence for a given key.

I tried aggr(max(Sequence),Key), but it is for some weird reason picking out line 1 and 5.

Attached a sample app.

Thank you in advance for getting me unstuck!

Cheers

Re: Problem with aggr max in straight table

PrashantSangle — Wed, 23 Sep 2015 07:55:43 GMT

Hi,

check firstsortedvalue()

Regards

Re: Problem with aggr max in straight table

Wed, 23 Sep 2015 08:02:51 GMT

ok, trying to figure out usage.

Re: Problem with aggr max in straight table

sasiparupudi1 — Wed, 23 Sep 2015 08:04:56 GMT

Remove sequence from your dimensions

hth

Sasi

Re: Problem with aggr max in straight table

Wed, 23 Sep 2015 08:08:13 GMT

The expected result is to keep row 6, and 7

Re: Problem with aggr max in straight table

jonathandienst — Wed, 23 Sep 2015 08:08:54 GMT

If you use Aggr() in a table, then all the table dimension should be included in the Aggr()

aggr(max(Sequence),Key,Status,Sequence,product)

(but you need to remove Sequence from the table dimensions and the aggr)

PS the Aggr() is redundant - =Max(Sequence) is enough

Re: Problem with aggr max in straight table

jonathandienst — Wed, 23 Sep 2015 08:13:14 GMT

But I think what you are perhaps after is to use the Aggr as a calculated dimension?

Re: Problem with aggr max in straight table

Wed, 23 Sep 2015 08:14:09 GMT

Tried it. It's picking out line 1, 5 and 6 ... ?

Re: Problem with aggr max in straight table

sasiparupudi1 — Wed, 23 Sep 2015 08:20:48 GMT

Please remove sequence from dimension and it should give you your result

Re: Problem with aggr max in straight table

Wed, 23 Sep 2015 08:35:14 GMT

Sorry i think i didnt express myself clearly.

Actually I dont need the expression field, i'm just using it to sort the table.

The expected result is as follows.

Thank you

Re: Problem with aggr max in straight table

Wed, 23 Sep 2015 08:37:39 GMT

I need to get the last value of the sequence, by Key, and that's why the grouping to get the latest status of the Key.

Re: Problem with aggr max in straight table

Wed, 23 Sep 2015 09:31:01 GMT

SasiParupudi1‌

Its the sequence 6 and 7 that i'm trying to get. Any other suggestions?

Thank you,

Re: Problem with aggr max in straight table

Wed, 23 Sep 2015 09:32:23 GMT

jontydkpi‌ Any other ideas ?

Re: Problem with aggr max in straight table

Wed, 23 Sep 2015 09:34:07 GMT

dreamer4 Didnt really find a solution with firstsortedvalue(). Any other ideas?

Thank you

Re: Problem with aggr max in straight table

PrashantSangle — Wed, 23 Sep 2015 10:21:44 GMT

Hi,

Please find attached file might help you

I did some modification in script like

tst:
LOAD Sequence,
     Status,
     Key,
     product
FROM
[..\..\..\..\..\..\..\Desktop\Test1.xls]
(biff, embedded labels, table is Sheet1$);

Test:
Load Sequence as New_Seq,
Status as new_status
Resident tst;

test1:
Load max(Sequence) as New_Seq,
Key as testK,
product as testProduct
// //Status as tst_Stats
Resident tst
Group by Key,product;

Then create stragiht table

with dimension testK,testProduct,new_status

and

Expression New_Seq

and check supress when value is Null from dimension tab for testK

Regards

Re: Problem with aggr max in straight table

Anonymous — Wed, 23 Sep 2015 10:31:05 GMT

hi,

in sort tab - select only numeric asc for sequence

Re: Problem with aggr max in straight table

sasiparupudi1 — Wed, 23 Sep 2015 10:42:22 GMT

may be change the sequence dimension to a calculated dimension with the expression

aggr(max(Sequence),Key)

hth

Sasi

Re: Problem with aggr max in straight table

Gabriel — Wed, 23 Sep 2015 11:03:55 GMT

Hi,

Using Straight Chart

In Dimension have a calculated dimension as follow

=aggr(max(Sequence),Key) // Rename this as whatever you want in

Then add Status, Key, Product to dimension

Then in the Expression tab

have same expression

=aggr(max(Sequence),Key)

Then go to Presentation tab HIDE the expression

This should give you your expected result

Re: Problem with aggr max in straight table

Wed, 23 Sep 2015 12:08:00 GMT

hello Gabriel,

With your solution it doesnt show the right status, it shows "New" for both, whereas it should display "Modified" and "Approved".

Re: Problem with aggr max in straight table

Wed, 23 Sep 2015 12:12:23 GMT

It works! actually i have a more complicated example, but to simplify i made a small table.

But no other solution in chart options?

Thank you anyway 🙂