Max(Datum)

[hansdevr]

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

[sunny_talwar]

Do you need this in the script or front end?

[hansdevr]

In the load script, by preference

[MayilVahanan]

Hi

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

[sunny_talwar · Accepted solution]

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;

[hansdevr]

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.

[surajap123]

Hi Sunny,

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

May i now the reason?

[sunny_talwar]

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.

[surajap123]

Hi Sunny,

Thanks for the reply.

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

[sunny_talwar]

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