Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi , I am loading data through below script . Not a sing record is fetching
[Desks]:
LOAD
DeskID,
Deskname,
DeskGroupID,
DeskUpdatedDate,
[Hedge/Spec]
FROM
(
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
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?
Sample attached
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.
How do you set your variables? Can you share the script for that?
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
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
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.