Recognise dateformat, convert and difference between 2 dates

[stevenpiers]

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!

[PrashantSangle]

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

[stevenpiers]

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

[shiveshsingh]

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);

[PrashantSangle]

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);

[stevenpiers]

I'm trying... see enclosed screendump. What am I doing wrong?

[qlikviewwizard · Accepted solution]

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);

[stevenpiers]

I believe this is the solution! Thanks all!

[qlikviewwizard]

Hi stevenpiers

Please mark the reply as Correct Answer.