Calculate only maximum date value

[pranshudatta]

Hi, I am facing an issue with my data load script.

I am trying to calculate the maximum (or the latest) date from my data but I am unable to do so as whenever I load data with the command "max(date) as maxDate", maxDate shows all date values and not the latest date value.

Following is a pseudocode of the data load script:

Load
[table1]:
a,
b,
c,
month,
year,
Date(Date#([yyyy]&[mm], 'YYYYMM') ,'YYYY-MM-DD') AS sampleDate;
Load
[res1]:
a as a_res,
b as b_res,
c as c_res,
sampleDate as sampleDate_res,
max(sampleDate) as sampleDateMax
resident [table1]
group by a, b, c sampleDate;
drop [table1];
The column "sampleDate" consists of date ranging from 01-01-2020 to 01-02-2022. I want a column in the new table that only displays the latest date (i.e 01-02-2022) in the following manner:

a_res   |   b_res  | c_res  |  sampleDate_res   | sampleDateMax
-----------------------------------------------------------------------------------
x           |    y            |  z          | 01-01-2020               |           -
w          |   q              | f          | 01-02-2022               | 01-02-2022
m          |   n             | l           |  01-09-2021              |          -
p           | j                 | o         | 01-02-2022               | 01-02-2022
b          | i                  |   v        | 01-11-2020              |         -

Requesting assistance to attain the above-mentioned table structure.

[vchuprina · Accepted solution]

Hi,

You see all values sample date values because you group them by all columns in your table.

In this example, you will have max data from all dataset

Exmple1:

[table1]:
Load
a,
b,
c,
month,
year,
Date(Date#([yyyy]&[mm], 'YYYYMM') ,'YYYY-MM-DD') AS sampleDate;

Left Join(table1)
[max]:
Load
Date(max(sampleDate)) as sampleDateMax
resident [table1];

Example2: 

Max data for the combination of a, b, c field

[table1]:
Load
a,
b,
c,
month,
year,
Date(Date#([yyyy]&[mm], 'YYYYMM') ,'YYYY-MM-DD') AS sampleDate;

Left Join(table1)
[max]:
Load
a,
b,
c,
Date(max(sampleDate)) as sampleDateMax
resident [table1]
group by a, b, c;

Regards,

Vitalii