Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to calculate the time needed for some scheduled exports. Loading into Qlikview isn't the problem, the only thing is that I cannot seem to get the dateformat right to calculate the time taken.
I've tried several solutions in the community, but unfortunately none were understandable enough for me or they just didn't seem to work.
If someone could help me with a sample file so I can 'watch and learn', would be very much appreciated!
Hi Try like this.
DateFormat:
LOAD *,Interval([Scheduled for]-[Last change],'D') AS DateDifferentDays;
LOAD Number,
DATE(
alt( Date#([Scheduled for],'MM/DD/YYYY h:mm[.fff] TT'),
Date#([Scheduled for],'DD-MM-YYYY h:mm')),'DD-MMM-YYYY') as [Scheduled for],
DATE(
alt( Date#([Last change],'MM/DD/YYYY h:mm[.fff] TT'),
Date#([Last change],'DD-MM-YYYY h:mm')),'DD-MMM-YYYY') as [Last change]
//[Last change]
//[Scheduled for]
FROM
Data\Export_Scheduled_TST.csv
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
You can achieve your requirement using alt() and interval()
alt() help you convert all date format into one date common date format
interval() help you to identify difference between date.
Regards
As much as I'd like to do as advised it would be helpfull to have a sample file. Perhaps my data doesn't work with these functions?
Thanks
Can you try this?
LOAD Number,
alt(timestamp#([Last change],'MM/DD/YYYY hh:mm:ss TT'),timestamp#([Last change],'MM-DD-YYYY hh:mm:ss')) as [Last change],
alt(timestamp#([Scheduled for],'MM/DD/YYYY hh:mm:ss TT'),timestamp#([Scheduled for],'MM-DD-YYYY hh:mm:ss')) as [Scheduled for]
FROM
[..\Desktop\Export_Scheduled_TST.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
Hi,
Shivesh already give you required solution
if you want date difference then use
Load *, interval([Scheduled for]-[Last change],'D') as date_difference_days;
LOAD Number,
alt(timestamp#([Last change],'MM/DD/YYYY hh:mm:ss TT'),timestamp#([Last change],'MM-DD-YYYY hh:mm:ss')) as [Last change],
alt(timestamp#([Scheduled for],'MM/DD/YYYY hh:mm:ss TT'),timestamp#([Scheduled for],'MM-DD-YYYY hh:mm:ss')) as [Scheduled for]
FROM
[..\Desktop\Export_Scheduled_TST.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
I'm trying... see enclosed screendump. What am I doing wrong?
Hi Try like this.
DateFormat:
LOAD *,Interval([Scheduled for]-[Last change],'D') AS DateDifferentDays;
LOAD Number,
DATE(
alt( Date#([Scheduled for],'MM/DD/YYYY h:mm[.fff] TT'),
Date#([Scheduled for],'DD-MM-YYYY h:mm')),'DD-MMM-YYYY') as [Scheduled for],
DATE(
alt( Date#([Last change],'MM/DD/YYYY h:mm[.fff] TT'),
Date#([Last change],'DD-MM-YYYY h:mm')),'DD-MMM-YYYY') as [Last change]
//[Last change]
//[Scheduled for]
FROM
Data\Export_Scheduled_TST.csv
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
I believe this is the solution! Thanks all!
Hi stevenpiers
Please mark the reply as Correct Answer.