Excel Load Compare Dates in WHERE

[johncahillhp]

Hello,

I see this question earlier,  Compare dates in WHERE clause while loading data from EXCEL however I am experiencing a similar problem whereby I cannot successfully compare dates in my Where clause. The intent is to load only records beyond a given date. In order to test that logic I am loading the last date and then all records with that date. There should only be one...however it returns 0.

MaxDT:
LOAD MAX(Start_DT) as A
FROM
[myfile.xlsx]
(ooxml, embedded labels, table is Records)
;
Let Wh_DateN = Peek('A');
Let Wh_DateS = date(Wh_DateN,'DD/MM/YYYY hh:mm:ss');
Drop Table MaxDT;
Trace 'Date number is $(Wh_DateN)';
Trace 'Date string is $(Wh_DateS)';

XCS:
LOAD *
FROM
[myfile.xlsx]
(ooxml, embedded labels, table is Records)
WHERE Start_DT = '$(Wh_DateN)'
;

MaxDT << Records 1 lines fetched
'Date number is 42188.692824074'
'Date string is 03/07/2015 16:37:40'
XCS << Records 0 lines fetched

Thanks in advance,

John

[MK_QSL]

You want to compare with TimeStamp or Date? If Date, use as below

MaxDT:

LOAD Date(Floor(MAX(Start_DT))) as A

FROM

[myfile.xlsx]

(ooxml, embedded labels, table is Records);

Let Wh_DateN = Peek('A');

Let Wh_DateS = Date(Wh_DateN,'DD/MM/YYYY');

Drop Table MaxDT;

Trace 'Date number is $(Wh_DateN)';

Trace 'Date string is $(Wh_DateS)';

XCS:

LOAD *

FROM

[myfile.xlsx]

(ooxml, embedded labels, table is Records)

WHERE Start_DT = '$(Wh_DateN)';

If TimeStamp, use as below

MaxDT:

LOAD TimeStamp((MAX(Start_DT))) as A

FROM

[myfile.xlsx]

(ooxml, embedded labels, table is Records);

Let Wh_DateN = Peek('A');

Let Wh_DateS = TimeStamp(Wh_DateN,'DD/MM/YYYY hh:mm:ss');

Drop Table MaxDT;

Trace 'Date number is $(Wh_DateN)';

Trace 'Date string is $(Wh_DateS)';

XCS:

LOAD *

FROM

[myfile.xlsx]

(ooxml, embedded labels, table is Records)

WHERE TimeStamp(Start_DT) = '$(Wh_DateN)';

[johncahillhp]

Thanks for the quick response. It is timestamp I am comparing. Using your suggestions these are my results

MaxDT_TS << Records 1 lines fetched
'Excel timestamp is 03/07/2015 4:37:40 PM'
'Formatted timestamp is 03/07/2015 16:37:40'
XCS << Records 0 lines fetched

The Where clause is using the timestamp (WHERE Timestamp(Start_DT) = '$(Wh_DateN)' ) but is still returns 0 records. There has to be one, as it was loaded in MaxDT.

Any other suggestions?

[MK_QSL]

Change below

Let Wh_DateS = TimeStamp(Timestamp#(Wh_DateN,'DD/MM/YYYY hh:mm:ss TT'));

[johncahillhp]

Here is a snapshot of the data using this load statement;

LOAD

Start_DT, Timestamp(Start_DT) as Start_DT_TS

Using either in the subsequent where clause returns 0 results.

[MK_QSL]

Can you provide sample excel file please?

[johncahillhp]

As requested

[johncahillhp · Accepted solution]

Looks like this request is not as straight forward as it would appear. It appears that the problem may be with the default number format used by Qlik in evaluating the Excel timestamp. I implemented a workaround to force the source system (BOE WEBI) to provide the timestamp in numeric format.

When pulling the new Start_DT_TS value with the existing LOAD statement I was still unable to find the same record in my second LOAD statement. Looking closer it appeared that Qlik was only pulling 9 decimal places. This resulted in an inaccurate timestamp value that could not be found in my second LOAD. SO I changed the LOAD to;

LOAD NUM(MAX(EVNT_STRT_TS),'0.##########') as C

The resulting value could then be used (as is) in my WHERE statement successfully.

Let nNEWXC_STRT_TS = Peek('C');

LOAD *
FROM [myfile.xlsx] (ooxml, embedded labels, table is Records)
WHERE Start_DT_TS > '$(nNEWXC_STRT_TS)'

I'll mark this as complete for those stumbling across similar conditions with Excel timestamps.

[johncahillhp]

Manish, thanks for your help. It would appear I found a workaround.