Strange problem with date converting

[#1]

Hello,

Could someone help me to fix the bug in date converting, please?

I've got a set of departure and arrival dates and the delivery-time. The problem is that some dates are in format DD.MM.YYY and some in MM.DD.YYYY.

To make it work correctly i wrote a script like:

Delivery:

LOAD

date(departure) as departure,

date(arrival) as arrival ,

     delivery_time,

    if(arrival-departure=delivery_time,

  Date(arrival),

  if(Date(Date#(arrival, 'MM.DD.YYYY'), 'DD.MM.YYYY') -Date(departure)=delivery_time,

  Date(Date#(arrival, 'MM.DD.YYYY'), 'DD.MM.YYYY'),

  IF(Date(arrival)-Date(Date#(departure, 'MM.DD.YYYY'), 'DD.MM.YYYY') =delivery_time,

  Date(arrival),

  IF(Date(Date#(arrival, 'MM.DD.YYYY'), 'DD.MM.YYYY') -Date(Date#(departure, 'MM.DD.YYYY'), 'DD.MM.YYYY') =delivery_time,

  Date(Date#(arrival, 'MM.DD.YYYY'), 'DD.MM.YYYY'),

  111

   )

   )

  )

  )as arrival1,

if(arrival-departure=delivery_time,

  Date(departure),

  if(Date(Date#(arrival, 'MM.DD.YYYY'), 'DD.MM.YYYY')-Date(departure)=delivery_time,

  Date(departure),

  IF(Date(arrival)-Date(Date#(departure, 'MM.DD.YYYY'), 'DD.MM.YYYY')=delivery_time,

  Date(Date#(departure, 'MM.DD.YYYY'), 'DD.MM.YYYY'),

  IF(Date(Date#(arrival, 'MM.DD.YYYY'), 'DD.MM.YYYY')-Date(Date#(departure, 'MM.DD.YYYY'), 'DD.MM.YYYY')=delivery_time,

  Date(Date#(departure, 'MM.DD.YYYY'), 'DD.MM.YYYY'),

  111

   )

   )

  )

  )as departure1

FROM

(qvd);

but somehow it works on one data-set and doesnt work on another 😃

When I put a text object on the sheet, I see a correct work of conversion, but in the tablebox there is a wrong data=(

Help me please to solve the problem and understand the logic of Qlik=)

Regards,

Alexa

[PrashantSangle]

Hi,

take a look at alt() in help menu it will helpful to acheive what you required.

Regards

[#3]

Hi Alexa,

Try to convert to num then to date.

Or only keep the num() and convert to date at the end.

Something like

= date(num(arrival),'DD.MM.YYYY')

[#4]

Hello, Max!

Thanks for a quick answer! I've never tried that function yet, thanks for your advice very much!

But I'm afraid it's not what I need, because there is only logical way to know the right date format:

departure   arrival          delivery_time

12.03.2014

13.01.2015

41

in such case 12.03.2014 is both correct in format DD.MM.YYYY and MM.DD.YYYY, as to "programm view", and logic says that departure should be 03.12.2014 to get the correct delivery time...

Correct me, if i'm not right, please

Regards,

Alexa

UPD:

as i found here Alt function question

my logic of alt is right .... so looking for an answer again...

(

MRKachhiaIMP 06.11.2014 5:46 (в ответ на Adam Pasierbek)

If you have two different formats like

DD/MM/YYYY and MM/DD/YYYY

There is no possibility to recognize them..

12/03/2014 and 12/03/2014.... Both could be either 3rd December or 12th March

ALT function can be used for more than one date formats something like

DD/MM/YYYY and DD.MM.YYYY and DD-MM-YYYY

or

DD/MM/YYYY and MM.DD.YYYY and YYY-MM-DD

Hope this will make clear...

)

[#5]

Hello,Sami! Thanks for your answer!

Num doesnt help =(

[PrashantSangle]

Hi,

As per my understanding alt() works like if()

Suppose 1st condition is true then it will assign first format, if not then go for second format

In your case suppose if you write

alt(date#(datefield,'DD.MM.YYYY'),date#(datefield,'MM.DD.YYYY'))

if your date is 12.03.2014 then you will get output as 12 as Day ,03 as Month and 2014 as Year

but if you write your expression

alt(date#(datefield,'MM.DD.YYYY'),date#(datefield,'DD.MM.YYYY'))

In this case you will get 12 as Month ,03 as Day and 2014 as Year

Regards

[burli1895]

Hi Alexa,

try this it works 100%.

LOAD
date(num(departure),'DD.MM.YYYY') as departure, 

date(num(arrival),'DD.MM.YYYY') as arrival , 

[Gabriel]

Hi,

See this blog post

[#9]

Thanks for your reply, Denis!

It works, but the result is same date format, as it was, as expected:

departure	arrival	delivery_time	departure1	arrival1
12.03.2014	13.01.2015	41	12.03.2014	111

[maxgro · Accepted solution]

I try with a chart, see attachment

First red rectangle, calc of the 4 difference

ok-ok     arrival - departure

rev-ok     arrival with month and day reversed - departure

ok-rev     arrival - departure with month and day reversed

rev-rev     ...............

Second red rectangle: compare the 4 difference with delivery time

Then, using that result, calc the NewArrival and NewDeparture

Last, calc NewArrival - NewDeparture (yellow), it should be as delivery_time (yellow)

If I understand, you can use the same logic at the script side