Loading date from excel

[Anonymous]

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

[sunny_talwar]

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?

[sunny_talwar]

Sample attached

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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

[sasiparupudi1]

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.