Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

stevenpiers
New Contributor II

Recognise dateformat, convert and difference between 2 dates

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!

Tags (1)
1 Solution

Accepted Solutions
qlikviewwizard
Honored Contributor II

Re: Recognise dateformat, convert and difference between 2 dates

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

Capture1.JPG

View solution in original post

8 Replies

Re: Recognise dateformat, convert and difference between 2 dates

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

Great dreamer's dreams never fulfilled, they are always transcended.
stevenpiers
New Contributor II

Re: Recognise dateformat, convert and difference between 2 dates

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
Honored Contributor

Re: Recognise dateformat, convert and difference between 2 dates

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

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

Great dreamer's dreams never fulfilled, they are always transcended.
stevenpiers
New Contributor II

Re: Recognise dateformat, convert and difference between 2 dates

I'm trying... see enclosed screendump. What am I doing wrong?QV-ExportScheduled.png

qlikviewwizard
Honored Contributor II

Re: Recognise dateformat, convert and difference between 2 dates

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

Capture1.JPG

View solution in original post

stevenpiers
New Contributor II

Re: Recognise dateformat, convert and difference between 2 dates

I believe this is the solution! Thanks all!

qlikviewwizard
Honored Contributor II

Re: Recognise dateformat, convert and difference between 2 dates

Hi stevenpiers

Please mark the reply as Correct Answer.