Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
hansdevr
Contributor II

Max(Datum)

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%ClaimVlgnrDatumReserve
990019914990019914-12016-06-06 20:25:31€ 239.419,78
990019914990019914-12016-06-06 20:21:01€ 240.938,57
990019914990019914-12016-06-06 20:14:14€ 242.325,84
990019914990019914-12016-06-06 19:56:03€ 282.325,84
990019914990019914-12016-06-06 19:52:32€ 283.006,66
990019914990019914-12016-04-17 21:34:13€ 285.000,85
990019914990019914-12016-04-04 11:10:47€ 286.000,85
990019914990019914-12016-03-30 21:02:23€ 291.500,00
990019914990019914-12016-03-04 09:16:06€ 295.500,00
990019914990019914-12016-03-02 14:10:48€ 300.000,00
990019914990019914-12016-02-12 08:55:16€ 11.150,29
990019914990019914-12015-04-16 10:26:52€ 14.500,00

Desired result:

   

%DossierNr%ClaimVlgnrDatumReserve
990019914990019914-12016-06-06€ 239.419,78
990019914990019914-12016-04-17€ 285.000,85
990019914990019914-12016-04-04€ 286.000,85
990019914990019914-12016-03-30€ 291.500,00
990019914990019914-12016-03-04€ 295.500,00
990019914990019914-12016-03-02€ 300.000,00
990019914990019914-12016-02-12€ 11.150,29
990019914990019914-12015-04-16€ 14.500,00
Tags (1)
1 Solution

Accepted Solutions

Re: Max(Datum)

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;

12 Replies

Re: Max(Datum)

Do you need this in the script or front end?

hansdevr
Contributor II

Re: Max(Datum)

In the load script, by preference

Re: Max(Datum)

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:31990019914-19900199141€ 239.419,78
2016-04-17 21:34:13990019914-19900199141€ 285.000,85
2016-04-04 11:10:47990019914-19900199141€ 286.000,85
2016-03-30 21:02:23990019914-19900199141€ 291.500,00
2016-03-04 09:16:06990019914-19900199141€ 295.500,00
2016-03-02 14:10:48990019914-19900199141€ 300.000,00
2016-02-12 08:55:16990019914-19900199141€ 11.150,29
2015-04-16 10:26:52990019914-19900199141€ 14.500,00

Re: Max(Datum)

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
Contributor II

Re: Max(Datum)

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
Contributor II

Re: Max(Datum)

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)

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
Contributor II

Re: Max(Datum)

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)

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

Community Browser