Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
johncahillhp
New Contributor III

Excel Load Compare Dates in WHERE

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

Tags (1)
1 Solution

Accepted Solutions
johncahillhp
New Contributor III

Re: Excel Load Compare Dates in WHERE

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.

View solution in original post

8 Replies
MVP
MVP

Re: Excel Load Compare Dates in WHERE

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
New Contributor III

Re: Excel Load Compare Dates in WHERE

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?

MVP
MVP

Re: Excel Load Compare Dates in WHERE

Change below

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

johncahillhp
New Contributor III

Re: Excel Load Compare Dates in WHERE

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.

MVP
MVP

Re: Excel Load Compare Dates in WHERE

Can you provide sample excel file please?

johncahillhp
New Contributor III

Re: Excel Load Compare Dates in WHERE

As requested

johncahillhp
New Contributor III

Re: Excel Load Compare Dates in WHERE

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.

View solution in original post

johncahillhp
New Contributor III

Re: Excel Load Compare Dates in WHERE

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