topic Re: Max Value in QlikView

Max Value

Fri, 20 Jul 2012 17:08:14 GMT

Good day

I have a data that shows me like that:

A	Date	C
1	01/01/2012	a
1	01/07/2012	b
3	01/03/2012	a
4	01/04/2012	a
4	01/05/2012	b
4	01/06/2012	c

Now i want to create another resident table from this information but with the max date grouping by the column 'A'.

Example, i want this:

A	Date	C
1	01/07/2012	b
3	01/03/2012	a
4	01/06/2012	c

I was trying this in script, but it didn't work:

noconcatenate

Load:

Date,

max(Date) as LastDate,

resident Table 1

Group by A;

Does anyone know how can i get this?

Re: Max Value

cesaraccardi — Fri, 20 Jul 2012 18:38:20 GMT

Hi Jorge,

In your resident load you have the aggregation max(Date) so all the other fields must be in the group by clause and not just the column A. Also keep in mind that if you load the aggregated field max(Date) is useless to load the unaggregated field Date in the same load.

Regards,

Cesar

Re: Max Value

Fri, 20 Jul 2012 19:56:02 GMT

Hello Cesar,

If I put all clauses in group by, example 'Group by A, C;' it will not consider the max date from each value in A, but it will consider the max date for group A and after for group C. This won't be the same information as the last table i posted in my comment.

Re: Max Value

cesaraccardi — Fri, 20 Jul 2012 20:20:05 GMT

Ok, so here is my suggestion:

TMP_TABLE:
LOAD * INLINE
[A,Date,C
1, 01/01/2012, a
    1, 01/07/2012, b
    3, 01/03/2012, a
    4, 01/04/2012, a
    4, 01/05/2012, b
    4, 01/06/2012, c
];
TABLE:
LOAD
          A,
          Date,
          A & '|' & Date as %key;
LOAD
     A          as A,
     Date(Max(Date)) as Date     
RESIDENT TMP_TABLE
GROUP BY
          A;
LEFT JOIN
LOAD
     A & '|' & Date as %key,
     C
RESIDENT
TMP_TABLE;
DROP TABLE TMP_TABLE;
DROP FIELD %key;

Re: Max Value

nagaiank — Fri, 20 Jul 2012 20:32:59 GMT

The following script also gives the desired result:

TMP_TABLE:

LOAD * INLINE

[A,Date,C

1, 01/01/2012, a

1, 01/07/2012, b

3, 01/03/2012, a

4, 01/04/2012, a

4, 01/05/2012, b

4, 01/06/2012, c

];

Table:

LOAD A

,FirstSortedValue(Date,-Date) as LastDate

,FirstSortedValue(C,-Date) as LastC

Resident TMP_TABLE Group By A;

Re: Max Value

Fri, 20 Jul 2012 20:35:40 GMT

Yeah i did the same as you, i used a couple of load resident and group by, but i was wondering if there was other way cause this data base has at about 1.000.000 lines and more than 20 columns, so to do 3 times a load is really hard in this table.

But thanks anyway.

Regards Jorge,

Re: Max Value

cesaraccardi — Fri, 20 Jul 2012 20:36:31 GMT

Nice, I didn't know about this function FirstSortedValue... thats a smarter way to do.

Thank you, krishnamoorthy.

Re: Max Value

Fri, 20 Jul 2012 20:52:52 GMT

Bravo krishnamoorthy

That's what i was looking for.

Many thanks my friend.

Regards,