Selecting the Max date for every field

igalindo — Sat, 16 Nov 2024 00:44:39 GMT

Hello everyone, I need some help with my syntax, Im just not able to figure it out.

Im using a table from the Charts widgets. Im adding each dimension but need to add one measurement based on the latest date for each field

I can explain it better like this: I have two tables

+-------------+------------+--------+
| Customer | Invoices | Paid? |
+-------------+------------+--------+
| Adidas | 352 | Yes |
| Nike | 198 | Yes |
| Puma | 58 | No |
| Nissan | 672 | Yes |
| Toyota | 103 | No |
| Coca Cola | 817 | Yes |
| Ford | 531 | No |
| Walmart | 216 | No |
| Kellogs | 392 | Yes |
+--------------+-----------+---------+

+-----------+----------+
| Customer | Payment Date |
+-----------+----------+
| Adidas | 21-09-20 |
| Nike | 05-11-19 |
| Puma | 17-01-20 |
| Nissan | 28-02-19 |
| Toyota | 18-01-21 |
| Coca Cola | 25-06-18 |
| Ford | 30-04-20 |
| Walmart | 31-12-20 |
| Kellogs | 03-03-19 |
| Adidas | 12-07-20 |
| Nike | 15-06-20 |
| Puma | 27-04-20 |
| Nissan | 03-12-19 |
| Toyota | 15-08-20 |
| Coca Cola | 18-02-20 |
| Ford | 01-08-19 |
| Walmart | 15-06-19 |
| Kellogs | 09-21-20 |
+-----------+----------+

So, the outcome I'm looking for is to show just one row per customer based on the latest date from the second table. Each customer has different payment dates and each customer has different most recent payment dates, I just want to display the latest ones.

the outcome should be something like :

+-------------+-----------+------+--------------+
| Customer | Invoices | Paid | Payment Date |
+-------------+-----------+------+--------------+
| Adidas | 352 | Yes | 21-09-20 |
| Nike | 198 | Yes | 15-06-20 |
| Puma | 58 | No | 27-04-20 |
+-------------+------------+------+--------------+

only one line per customer and Payment Date column only displays the latest one

Im kind of new in this, and I would appreciate if I can be given an expression to insert into my Analysis page, (table chart widget) as Im not too good with the data load editor

Re: Selecting the Max date for every field

PriyankaShivhare — Thu, 28 Jan 2021 02:55:35 GMT

MapMaxDate:

Mapping Load Customer,
MaxPaymentDate
;

Load Customer,
max(Date(Date#(Payment_Date,'DD-MM-YY'),'DD-MM-YY')) as MaxPaymentDate

group by Customer;

Load Customer,
Payment_Date

Inline [

Customer , Payment_Date

Adidas , 21-09-20
Nike , 05-11-19
Puma , 17-01-20
Nissan , 28-02-19
Toyota , 18-01-21
Coca Cola , 25-06-18
Ford , 30-04-20
Walmart , 31-12-20
Kellogs , 03-03-19
Adidas , 12-07-20
Nike , 15-06-20
Puma , 27-04-20
Nissan , 03-12-19
Toyota , 15-08-20
Coca Cola , 18-02-20
Ford , 01-08-19
Walmart , 15-06-19
Kellogs , 09-21-20

];

T2:

Load * ,
ApplyMap('MapMaxDate',Customer,null()) as MaxDate

Inline [

Customer , Invoices , Paid?

Adidas , 352 , Yes
Nike , 198 , Yes
Puma , 58 , No
Nissan , 672 , Yes
Toyota , 103 , No
Coca Cola , 817 , Yes
Ford , 531 , No
Walmart , 216 , No
Kellogs , 392 , Yes ];

Hope this Helps!

Thanks,
Priyanka

topic Selecting the Max date for every field in App Development

Selecting the Max date for every field

Re: Selecting the Max date for every field