topic Re: Max(Datum) in QlikView

Max(Datum)

hansdevr — Wed, 15 Jun 2016 10:53:59 GMT

What I need is to simpify this table.. In the original table, there are (potentially) many transactions on one day, hence the hh:mm:ss in the date. What I want is in the resulting table: when there are more than 1 transactions on one day, I need to keep the most recent transaction for that day, with the corresponding reserve amount. Days can then lose the hh:mm:ss extensions, so I get one final transaction per whole day.

Again, I think this should be accomplished with max() and group by.. But how? I did a few attempts, but couldn't get it to work.

See example below:

Original:

%DossierNr	%ClaimVlgnr	Datum	Reserve
990019914	990019914-1	2016-06-06 20:25:31	€ 239.419,78
990019914	990019914-1	2016-06-06 20:21:01	€ 240.938,57
990019914	990019914-1	2016-06-06 20:14:14	€ 242.325,84
990019914	990019914-1	2016-06-06 19:56:03	€ 282.325,84
990019914	990019914-1	2016-06-06 19:52:32	€ 283.006,66
990019914	990019914-1	2016-04-17 21:34:13	€ 285.000,85
990019914	990019914-1	2016-04-04 11:10:47	€ 286.000,85
990019914	990019914-1	2016-03-30 21:02:23	€ 291.500,00
990019914	990019914-1	2016-03-04 09:16:06	€ 295.500,00
990019914	990019914-1	2016-03-02 14:10:48	€ 300.000,00
990019914	990019914-1	2016-02-12 08:55:16	€ 11.150,29
990019914	990019914-1	2015-04-16 10:26:52	€ 14.500,00

Desired result:

%DossierNr	%ClaimVlgnr	Datum	Reserve
990019914	990019914-1	2016-06-06	€ 239.419,78
990019914	990019914-1	2016-04-17	€ 285.000,85
990019914	990019914-1	2016-04-04	€ 286.000,85
990019914	990019914-1	2016-03-30	€ 291.500,00
990019914	990019914-1	2016-03-04	€ 295.500,00
990019914	990019914-1	2016-03-02	€ 300.000,00
990019914	990019914-1	2016-02-12	€ 11.150,29
990019914	990019914-1	2015-04-16	€ 14.500,00

Re: Max(Datum)

sunny_talwar — Wed, 15 Jun 2016 10:56:51 GMT

Do you need this in the script or front end?

Re: Max(Datum)

hansdevr — Wed, 15 Jun 2016 11:04:29 GMT

In the load script, by preference

Re: Max(Datum)

MayilVahanan — Wed, 15 Jun 2016 11:05:49 GMT

Try like this

Test:

LOAD *, Floor(Datum) as Date INLINE [

%DossierNr, %ClaimVlgnr, Datum, Reserve

990019914, 990019914-1, 2016-06-06 20:25:31, "€ 239.419,78"

990019914, 990019914-1, 2016-06-06 20:21:01, "€ 240.938,57"

990019914, 990019914-1, 2016-06-06 20:14:14, "€ 242.325,84"

990019914, 990019914-1, 2016-06-06 19:56:03, "€ 282.325,84"

990019914, 990019914-1, 2016-06-06 19:52:32, "€ 283.006,66"

990019914, 990019914-1, 2016-04-17 21:34:13, "€ 285.000,85"

990019914, 990019914-1, 2016-04-04 11:10:47, "€ 286.000,85"

990019914, 990019914-1, 2016-03-30 21:02:23, "€ 291.500,00"

990019914, 990019914-1, 2016-03-04 09:16:06, "€ 295.500,00"

990019914, 990019914-1, 2016-03-02 14:10:48, "€ 300.000,00"

990019914, 990019914-1, 2016-02-12 08:55:16, "€ 11.150,29"

990019914, 990019914-1, 2015-04-16 10:26:52, "€ 14.500,00"

];

Left Join

LOAD Date, Max(Datum) as MaxDatum Resident Test

Group by Date;

Final:

LOAD *, if(Datum = MaxDatum, 1, 0) As Flag Resident Test;

DROP Table Test;

Datum	%ClaimVlgnr	%DossierNr	Flag	=Only({<Flag = {1}>}Reserve)

2016-06-06 20:25:31	990019914-1	990019914	1	€ 239.419,78
2016-04-17 21:34:13	990019914-1	990019914	1	€ 285.000,85
2016-04-04 11:10:47	990019914-1	990019914	1	€ 286.000,85
2016-03-30 21:02:23	990019914-1	990019914	1	€ 291.500,00
2016-03-04 09:16:06	990019914-1	990019914	1	€ 295.500,00
2016-03-02 14:10:48	990019914-1	990019914	1	€ 300.000,00
2016-02-12 08:55:16	990019914-1	990019914	1	€ 11.150,29
2015-04-16 10:26:52	990019914-1	990019914	1	€ 14.500,00

Re: Max(Datum)

sunny_talwar — Wed, 15 Jun 2016 11:12:53 GMT

You can try this:

Table:

LOAD %DossierNr,

%ClaimVlgnr,

Datum,

Reserve,

Date(Floor(Datum)) as Date

FROM

[https://community.qlik.com/thread/220956]

(html, codepage is 1252, embedded labels, table is @1);

Right Join (Table)

LOAD %DossierNr,

%ClaimVlgnr,

Max(Datum) as Datum,

Date

Resident Table

Group By %DossierNr, %ClaimVlgnr,Date;

Re: Max(Datum)

hansdevr — Wed, 15 Jun 2016 11:42:25 GMT

Both Sunny and Mayil, thank you very much! I chose for Sunny's solution, because I think it was the most straightforward and simple solution - a solution I immediately grasped the essence from. Mayil's solution works, but it introduced some extra steps.

Re: Max(Datum)

surajap123 — Wed, 15 Jun 2016 12:02:48 GMT

Hi Sunny,

You did right join and in the 2nd table(aggregate table) you didn't mention Reserve field.

May i now the reason?

Re: Max(Datum)

sunny_talwar — Wed, 15 Jun 2016 12:17:45 GMT

Since I am doing the right join, my intention was to remove rows from 1st table which doesn't exist in the 2nd table. adding Reserve is not really necessary, but can be added using FirstSortedValue() function.

Re: Max(Datum)

surajap123 — Wed, 15 Jun 2016 12:32:59 GMT

Hi Sunny,

Thanks for the reply.

I am wondering, can't we achieve the result by doing group by directly in the first table.

Re: Max(Datum)

sunny_talwar — Wed, 15 Jun 2016 12:35:42 GMT

That's possible. There are multiple ways to do this. Group By, Join, Peek/Previous, Flags with Where. Which one of them to use? Depends on which one performs the best

Re: Max(Datum)

bharath28 — Wed, 15 Jun 2016 13:04:34 GMT

Thanks a lot. Its always great to see your answers.

If you have time, could you give the UI solution( i mean chart expression).

Re: Max(Datum)

sunny_talwar — Wed, 15 Jun 2016 13:23:29 GMT

bharath28‌ -

In addition to mayilvahanan‌'s front end method, you can use FirstSortedValue()

Re: Max(Datum)

bharath28 — Wed, 15 Jun 2016 13:37:04 GMT

Thank you so much