Set a Max(Date) Variable to filter a Load Script

[mcrea22]

I am trying to load the following data from the attached excel file and limit the import to those rows where the (single) Maximum date for the DOC_DATE column is present. I 've been able to set a variable (below) and get the system to recognize the max(date) function but the variable is returning MX_DATE as every date in the data set not just a single Maximum Date. How do I fix this?

CDASS_TMP:
LOAD *
From [lib://AttachedFiles/CDASS 9-19-22.xlsx](ooxml, embedded labels, table is CDASS)
;

max_doc_date:
LOAD Date(Max(DOC_DATE)) as MX_DATE
Resident CDASS_TMP
GROUP BY DOC_DATE
;

LET v_date = Peek('MX_DATE');

DROP Table max_doc_date;

 CDASS_VIEW:
 LOAD *
 From [lib://AttachedFiles/CDASS 9-19-22.xlsx](ooxml, embedded labels, table is CDASS)
 Where DOC_DATE = $(v_date);

[MendyS]

Hi @mcrea22 

Try this - 

CDASS_TMP:
LOAD Date(Max(DOC_DATE)) as MX_DATE
From [lib://AttachedFiles/CDASS 9-19-22.xlsx](ooxml, embedded labels, table is CDASS)
GROUP BY DOC_DATE
;

Inner join(CDASS_TMP)
 LOAD *,DOC_DATE as MX_DATE
 From [lib://AttachedFiles/CDASS 9-19-22.xlsx](ooxml, embedded labels, table is CDASS)

;

Drop Field MX_DATE from CDASS_TMP;

[mcrea22]

Same result. I still get multiple dates not just rows with the max date present.

[mcrea22]

Removing the Group by statement fixes the issue of duplicates. I initially I was using it to to extract a Max Date from the full data set because the Max function fails when anything that is not aggregated is not grouped. However, if the Max(Date) is the only value in the LOAD/Select 'Group by' will generate a max(date) for each Date group.

Here's how the final (working) product looks. Thanks @MendyS 

CDASS_TMP:
LOAD Date(Max(DOC_DATE), 'DD/MM/YYYY') as MX_DATE
From [lib://AttachedFiles/CDASS 9-19-22.xlsx](ooxml, embedded labels, table is CDASS)
;

Inner join(CDASS_TMP)
LOAD *,DOC_DATE as MX_DATE
From [lib://AttachedFiles/CDASS 9-19-22.xlsx](ooxml, embedded labels, table is CDASS)

;

Drop Field MX_DATE from CDASS_TMP;

[Dalton_Ruer]

Couple of issues above:

1. You need single quotes around your variable expansion

2. By using the Group By DOC_DATE you are forcing the multiples. Since you are trying for a single date, you don't need to do the GROUP BY. 

I put some sample code from Inline load below. Also notice there is no reason to go back to your EXCEL document. I simply used the RESIDENT load instead and used the NOCONCATENATE to force second table. 

CDASS_TMP:
Load * Inline [
FieldA, DOC_DATE
23, 02/03/2022
24, 02/05/2022
35, 02/05/2022
36, 02/02/2022
48, 02/05/2022
];

max_doc_date:
LOAD Date(Max(DOC_DATE)) as MX_DATE
Resident CDASS_TMP;
;

LET v_date = Peek('MX_DATE');

CDASS_VIEW:
NoConcatenate LOAD *
resident CDASS_TMP
Where DOC_DATE = '$(v_date)';

drop table CDASS_TMP;

[mcrea22]

As it turns out the lack of '' around the expansion variable in the WHERE statement and GROUP BY in the variable definition query were at the root of the problem.

I had noticed the no concatenate was necessary and added it earlier, but was finding that the new table was rendering 0 rows and couldn't understand why. Going back to the source table was something I tried just to rule the resident table out as the source of the issue. 

@Dalton_Ruer  Might you have a good resource detailing what '' is doing ? I was familiar with $() to call a variable but haven't seen '' around it when using them on the front end. Imagine it is syntax specific to the Load editor

[Dalton_Ruer]

You were doing the right thing get the value of the variables. It would export something like 2/21/2022, but if you hand typed the expression you would have typed = '2/21/2022' 

The nice thing, is that the dollar sign expansion will always occur. So try things by hand, then back in the $(variable) to what you have hand typed.