Selecting the Max date for every field

[igalindo]

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

T1

+-------------+------------+--------+
| 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    |
+--------------+-----------+---------+

T2

+-----------+----------+
| 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

[PriyankaShivhare]

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