topic Re: Loading date from excel in QlikView

Loading date from excel

Anonymous — Tue, 01 May 2018 11:47:25 GMT

Hi , I am loading data through below script . Not a sing record is fetching

[Desks]:
LOAD

DeskID,
Deskname,
DeskGroupID,
DeskUpdatedDate,
[Hedge/Spec]

FROM

(ooxml, embedded labels, table is Sheet2)
WHERE
DeskUpdatedDate >'17-Apr-2018 08:10:00.000'
AND
DeskUpdatedDate <= '01-May-2018 11:19:37.137'

can any one please help , why not a sing record is loading

Re: Loading date from excel

sunny_talwar — Tue, 01 May 2018 11:53:23 GMT

May be try this

[Desks]:
LOAD DeskID,
     Deskname,
     DeskGroupID,
     DeskUpdatedDate,
     [Hedge/Spec]
FROM
(ooxml, embedded labels, table is Sheet2)
Where DeskUpdatedDate > MakeDate(2018, 4, 17) + MakeTime(8, 10) and DeskUpdatedDate <= MakeDate(2018, 5, 1) + MakeTime(11, 19, 37);

Do you need the millisecond part also?

Re: Loading date from excel

sunny_talwar — Tue, 01 May 2018 11:56:14 GMT

Sample attached

Re: Loading date from excel

Anonymous — Tue, 01 May 2018 11:56:46 GMT

Hi Sunny , Thanks for your prompt reply . But I don't want to make change in hardcoded date as this date is coming through variable . Can you look into excel data that I have attached and if any format change is required for DeskUpdatedDate field.

Re: Loading date from excel

sunny_talwar — Tue, 01 May 2018 11:58:19 GMT

How do you set your variables? Can you share the script for that?

Re: Loading date from excel

Anonymous — Tue, 01 May 2018 12:03:05 GMT

Actually , in live environment , this variable is set through subroutine . and DeskUpdatedDate is date time field in DB but here for my learning purpose , I just copied the data of query in excel and doing play around . But DeskUpdatedDate is causing an issue for me as it is no more date time field .

I am okay to do any change in my load script with DeskUpdatedDate column

Re: Loading date from excel

sunny_talwar — Tue, 01 May 2018 12:07:36 GMT

Your date field doesn't seem to be the issue, but the date field you are checking against (your variable) is... your variable is read as a string rather than date which is why when you compare date to a string.... nothing comes through.... in order to fix this, you can either use Date#() function to help interpret QlikView your string as a date or you can play around with environmental variables to make sure they match exactly with the date format and then you probably won't need to use Date#() function

Re: Loading date from excel

sasiparupudi1 — Tue, 01 May 2018 18:16:54 GMT

May be try

Desks:

LOAD DeskID,

Deskname,

DeskGroupID,

Timestamp(DeskUpdatedDate,'M/DD/YYYY h:mm:ss TT') AS DeskUpdatedDate,

[Hedge/Spec]

FROM

[Test123.xlsx]

(ooxml, embedded labels, table is Sheet2)

where Timestamp(DeskUpdatedDate,'DD-MMM-YYYY hh:mm:ss.fff') >'17-Apr-2018 08:10:00.000'

AND

Timestamp(DeskUpdatedDate,'DD-MMM-YYYY hh:mm:ss.fff') <= '01-May-2018 11:19:37.137';

Note that the right hand side values must be a timestamps as well otherwise the comparison fails.