topic Re: Recognise dateformat, convert and difference between 2 dates in QlikView

Recognise dateformat, convert and difference between 2 dates

stevenpiers — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Recognise dateformat, convert and difference between 2 dates

PrashantSangle — Wed, 23 May 2018 12:10:35 GMT

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

Re: Recognise dateformat, convert and difference between 2 dates

stevenpiers — Wed, 23 May 2018 12:25:36 GMT

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

Re: Recognise dateformat, convert and difference between 2 dates

shiveshsingh — Wed, 23 May 2018 12:25:39 GMT

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

Re: Recognise dateformat, convert and difference between 2 dates

PrashantSangle — Wed, 23 May 2018 12:33:02 GMT

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

Re: Recognise dateformat, convert and difference between 2 dates

stevenpiers — Wed, 23 May 2018 12:46:29 GMT

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

Re: Recognise dateformat, convert and difference between 2 dates

qlikviewwizard — Wed, 23 May 2018 13:13:17 GMT

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

Re: Recognise dateformat, convert and difference between 2 dates

stevenpiers — Wed, 23 May 2018 13:31:43 GMT

I believe this is the solution! Thanks all!

Re: Recognise dateformat, convert and difference between 2 dates

qlikviewwizard — Wed, 23 May 2018 14:44:54 GMT

Hi stevenpiers

Please mark the reply as Correct Answer.