Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
take a look at alt() in help menu it will helpful to acheive what you required.
Regards
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')
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...
)
Hello,Sami! Thanks for your answer!
Num doesnt help =(
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
Hi Alexa,
try this it works 100%.
LOAD
date(num(departure),'DD.MM.YYYY') as departure,
date(num(arrival),'DD.MM.YYYY') as arrival ,
Hi,
See this blog post
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 |
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