topic Excel Load Compare Dates in WHERE in QlikView

Excel Load Compare Dates in WHERE

johncahillhp — Wed, 08 Jul 2015 17:26:21 GMT

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

Re: Excel Load Compare Dates in WHERE

MK_QSL — Wed, 08 Jul 2015 17:30:16 GMT

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)';

Re: Excel Load Compare Dates in WHERE

johncahillhp — Wed, 08 Jul 2015 17:44:32 GMT

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?

Re: Excel Load Compare Dates in WHERE

MK_QSL — Wed, 08 Jul 2015 17:56:28 GMT

Change below

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

Re: Excel Load Compare Dates in WHERE

johncahillhp — Wed, 08 Jul 2015 17:56:56 GMT

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.

Re: Excel Load Compare Dates in WHERE

MK_QSL — Wed, 08 Jul 2015 18:06:35 GMT

Can you provide sample excel file please?

Re: Excel Load Compare Dates in WHERE

johncahillhp — Wed, 08 Jul 2015 18:34:40 GMT

As requested

Re: Excel Load Compare Dates in WHERE

johncahillhp — Thu, 09 Jul 2015 18:54:50 GMT

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.

Re: Excel Load Compare Dates in WHERE

johncahillhp — Thu, 09 Jul 2015 18:55:42 GMT

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