Calculate only maximum date value

pranshudatta — Thu, 21 Apr 2022 12:02:11 GMT

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.

Re: Calculate only maximum date value

vchuprina — Thu, 21 Apr 2022 12:24:06 GMT

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

topic Calculate only maximum date value in App Development

Calculate only maximum date value

Re: Calculate only maximum date value