How to select the first expiring date (min function for date)

[aryx]

Dears,

I need to select, from an excel file, the first expiring date for a material code.

As example:

Material code	Date field
12345	01.05.2022
12345	12.11.2020
12345	18.06.2021
6789	31.12.9999
6789	14.09.2021
3210	31.12.9999

Result table:

Material code	Date field
12345	12.11.2020
6789	14.09.2021
3210	31.12.9999

Here I have three problems:

1) Date value format with dots "." looks to not be recognized as date, therefore if I try then to select the minimum value (first expiring date) I get -- as result in the table of the sheet.

2) Date 31.12.9999 is frequently used by our SAP user to define a not expiring material code as it looks as not a real date, I need to show it, if it is the unique date for a specific material code

3) have the min function working with dates

I tried several statements in the script from showing the value as it is, without any conversion, just aggregating data with min function to applying conversions to data and replacing "." with "/" without any success:

min("Date field")

date#(alt("Date field",'DD.MM.YYYY','dd-mmm-yyyy')) 

min(Date#(Date("Date field")))

Date(Date#(min(num(right("Date field",4)&mid("Date field",4,2)&left("Date field",2)))))

Date(min("Date field"))

min(Date(Date#(replace("Valid to",'.','/'))))

The results I get is usually -- or  28-Aug-275447 which I honestly don't know where it come from.

Do you have any suggestion?

The load I use is pretty simple:

LOAD
Material,
Min(num(right("Date field",4)&mid("Date field",4,2)&left("Date field",2))) as "Date field"

FROM [lib://NPI Data Readiness (eu_anori2)/PHARM REPORTING/SAP/ERP2 YI031.xls]
(txt, unicode, embedded labels, delimiter is '\t', msq, header is 3 lines)

group by Material;

[ibdK4evr · Accepted solution]

Hi,

I hope this helps , i have added the load script. In the load script the QlikTest  table has the actual data , then from the QlikTest another table is resident loaded to get one extra column which converts the date into a num equivalent for min calculation and the table name is QlikTestTransformed. I have added the table chart screenshot for your reference as well.

QlikTest:
Load * Inline
[
Material code, Date field
12345, 01.05.2022
12345, 12.11.2020
12345, 18.06.2021
6789, 31.12.9999
6789, 14.09.2021
3210, 31.12.9999
];

QlikTestTransformed:
load [Material code],
[Date field],
Num(SubField([Date field],.,3)&SubField([Date field],.,2)&SubField([Date field],.,1)) as dates resident QlikTest;

Drop Table QlikTest;

Screenshot of table object with min calculation of date:

[aryx]

Hi,

I tried this solution and indeed works if I put the min() function in the table instead of in the script.

Won't table take more time to show data?

Furthermore, I won't see the field "dates" in the table but just the "Data Field", due to the fact I do have several columns reporting such kind of data and value is dates column would have a non-readable format.

I also tried to move the min() function in the script, as below:

min(Num(SubField("Date field",.,3)&SubField("Date field",.,2)&SubField("Date field",.,1))) as dates 

but then I need to group by date field as well, which will give a different result:

LOAD
Material,
"Date field",
Min(Num(SubField("Date field",.,3)&SubField("Date field",.,2)&SubField("Date field",.,1))) as "Date Field min"
resident table;
group by Material,"Date field";

Instead if I remove Date field of course I get the error, as usually for SQL.