Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
Do you need this in the script or front end?
In the load script, by preference
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 |
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;
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.
Hi Sunny,
You did right join and in the 2nd table(aggregate table) you didn't mention Reserve field.
May i now the reason?
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.
Hi Sunny,
Thanks for the reply.
I am wondering, can't we achieve the result by doing group by directly in the first table.
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