topic Re: Strange problem with date converting in QlikView

Strange problem with date converting

Wed, 10 Jun 2015 10:11:06 GMT

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

Re: Strange problem with date converting

PrashantSangle — Wed, 10 Jun 2015 10:14:28 GMT

Hi,

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

Regards

Re: Strange problem with date converting

Wed, 10 Jun 2015 10:26:06 GMT

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

Re: Strange problem with date converting

Wed, 10 Jun 2015 10:30:39 GMT

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

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

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

Hope this will make clear...

)

Re: Strange problem with date converting

Wed, 10 Jun 2015 10:42:31 GMT

Hello,Sami! Thanks for your answer!

Num doesnt help =(

Re: Strange problem with date converting

PrashantSangle — Wed, 10 Jun 2015 11:33:15 GMT

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

Re: Strange problem with date converting

burli1895 — Wed, 10 Jun 2015 11:36:14 GMT

Hi Alexa,

try this it works 100%.

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

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

Re: Strange problem with date converting

Gabriel — Wed, 10 Jun 2015 12:45:49 GMT

Hi,

See this blog post

Re: Strange problem with date converting

Wed, 10 Jun 2015 12:57:06 GMT

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

Re: Strange problem with date converting

maxgro — Wed, 10 Jun 2015 18:08:19 GMT

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

Re: Strange problem with date converting

Thu, 18 Jun 2015 09:50:21 GMT

Massimo, thank you very much!!! Your idea works great!!!! "You saved our lifes, we are totally gratefull"

Could you be so kind to tell me (any suggestion about qliks logic in this case), why my script didnt work, althoug the same code in the text object works good?

Best regards,

Alexa