Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
aryx
Contributor
Contributor

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

Dears,

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

As example:

Material codeDate field
1234501.05.2022
1234512.11.2020
1234518.06.2021
678931.12.9999
678914.09.2021
321031.12.9999

 

Result table:

Material codeDate field
1234512.11.2020
678914.09.2021
321031.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;

1 Solution

Accepted Solutions
ibdK4evr
Contributor III
Contributor III

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:

Screenshot 2020-12-05 at 7.20.12 PM.png

View solution in original post

2 Replies
ibdK4evr
Contributor III
Contributor III

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:

Screenshot 2020-12-05 at 7.20.12 PM.png

aryx
Contributor
Contributor
Author

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.