Dates in load script not being treated as dates (maybe?)

[Oggy172]

I've got a bit of an issue I've come across, when migrating an existing report from QV to QS.

It performs an interval match to apply 'costs' at a point in time to their production date.

In the existing report, it is taking a Date field from a qvd, that was output as YYYYYMMDD --

if(text(date("Production Date-Time", 'YYYYMMDD')) <> '17530101',
   date(floor("Posting Date"), 'YYYYMMDD'),
   date(floor("Posting Date"))) as JOIN_Date;

When this is loaded back into a QV document - it is taking this as DD/MM/YYYY format, I presume due to the DateFormat='DD/MM/YYYY' section application

LOAD 
'x-' & RowNo()	AS	pId,
1		AS	ProductionFlag,
0		AS	ConsumptionFlag,		
'1-' & [Entry No_] & '-' & JOIN_item & '-' & Company			AS	JOIN_PRODUCTION_SUMMARY, 	
JOIN_item & '-' & Date(JOIN_Date, 'YYYYMMDD')	AS	JOIN_PROD_ITEM_RC, 
Quantity	AS 	[BOM Allocated Quantity],
[Entry No_], 
Company, 
UPPER(Company)	AS	SECURITY_COMPANY,
JOIN_item, 
[Production BOM No_], 
Date, 
[Entry Type], 
[Entry Type Desc], 
[Production BOM No_] & '-' & Date(JOIN_Date, 'YYYYMMDD')		AS	JOIN_BoM_ILE, 
		[Source No_], 
		[Document No_], 
		[Location Code],  
		[Production Date-Time], 
		JOIN_Date
	FROM
		// D:\qlik\datasources\qvd\ILE.Production.qvd
		[D:\qlik\datasources\Qlikview - QlikSaaS\ILE.Production.qvd]
	(qvd)
	where text(date(JOIN_Date,'YYYYMMDD')) >='20220703';

and these are right justified, which suggests they are correctly formatted as a date.

However, when I've ported these over to QlikSense, I get strange behaviour - the output to .qvd works the same

Creation of .qvd

DateFormat

Load Script

LOAD 
'x-' & RowNo()	AS	pId,
1		AS	ProductionFlag,
0		AS	ConsumptionFlag,		
'1-' & [Entry No_] & '-' & JOIN_item & '-' & Company			AS	JOIN_PRODUCTION_SUMMARY, 

JOIN_item & '-' & Date(JOIN_Date, 'YYYYMMDD')					AS	JOIN_PROD_ITEM_RC, 

Quantity	AS 	[BOM Allocated Quantity],
[Entry No_], 
Company, 
UPPER(Company)	AS	SECURITY_COMPANY,
JOIN_item, 
[Production BOM No_], 
Date, 
[Entry Type], 
[Entry Type Desc], 
[Production BOM No_] & '-' & Date(JOIN_Date, 'YYYYMMDD')		AS	JOIN_BoM_ILE, 

[Source No_], 
[Document No_], 
[Location Code],  
[Production Date-Time], 
JOIN_Date

FROM	
[lib://QVD Data:Office_365_Sharepoint - a@b.com/Shared Documents/QlikSaaS/BC Testing/qvd/BC_ILE_Production.qvd]	(qvd)	
where 
date(JOIN_Date) >=44745;

but the date outputs are not converted back

date(JOIN_Date, 'DD/MM/YYYY') as JOIN_Date left justifies them, so fail the interval match

Can someone put me out of my misery? 

[marksouzacosta]

Hi @Oggy172,

There are lots of things I would like to review in your code but, you can try the following:

Use Date(Date#(JOIN_Date, 'YYYYMMDD'), 'YYYYMMDD')
instead of Date(JOIN_Date, 'YYYYMMDD')

Date# function is telling to Qlik that JOIN_Date values are date values with the format 'YYYYMMDD'.
While Date is formatting the date value to the 'YYYYMMDD' format.
So, first you are making sure that the values are properly converted to date and then formatting to YYYYMMDD. Null values are indications of problems.

Also I would avoid converting dates to text to do data comparison.
For example, in your where clause, you could do this:
where date#(JOIN_Date,'YYYYMMDD') >= MakeDate(2022,7,3);

Regards,

Mark Costa

[Oggy172]

Hi @marksouzacosta 

Many thanks for your reply, however I still get the same behaviour in that I get a left justified "date" and no null values.

I changed it to DD/MM/YYYY as that's the format in which my timestamps I need to join, are in.

[Oggy172]

I believe it may have been working ok and there just wasnt enough data in my tables to fall within the interval match.

Still strange that Qlik isn't applying the default formatting