Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Ive got a relative simple question regarding changing the date in a script.
Ive got the following LOAD statement:
LOAD
date(orderdate+26298) AS OrderDate,
date(del_date+26298) AS DelDate;
I want this statement to be conditional. In words, orderdate+26298 IF there is a value in the date field. If not then don't add this number and let it empty.
In my problem it adds a value to the date even if the field is empty, so I get a date out of nothing.
Any suggestions?
I think you are right about the formating. Now this is what Ive got, the raw input for the orderdate fields are numbers like, '234234' or an empty field is shown as 0 in the qlikview table viewer.
When I connect a master calendar to the dates it will automatically modify the this to dates like, '26-04-2010' or 0 is given as 31-12-71.
So I have to do something with formatting?
EDIT:
Many thanks! I fiddled arround a bit with your suggestions.
if(orderdate<>'0',date(orderdate+26298),'') AS OrderDate,
This seemed to work now. It was indeed a matter of finding the actual format. Thanks for the tip!
If(len(orderdate)>0,date(orderdate+26298)) as OrderDate
Hi,
perhaps you can try to condition the loading of these fields.
As for example:
LOAD
If(ISNULL(orderDate),'',Date(orderDate + 26298)) As OrderDate,
If(ISNULL(del_date),'',Date(del_date + 26298)) As DelDate);
LOAD
orderdate, del_date;
HTH
Regards,
Thanks for helping. I tried your solutions. However somehow I was wrong initially. Qlikview interpretates my empty database value as the date: '30-12-99'
So this changes things a bit, I would like: if 30-12-99 then set it to empty.
Suggestions?
try this:
LOAD
if(order date<>'30-12-99',date(orderdate+26298),'') AS OrderDate,
date(del_date+26298) AS DelDate;
If the value is in the format you wrote, try to just modify the conditional:
LOAD
If(text(orderDate)='30-12-99','',Date(orderDate + 26298)) As OrderDate,
If(text(del_date)='30-12-99','',Date(del_date + 26298)) As DelDate);
LOAD
orderdate, del_date;
But check what is the original format of the data for these fields, just in case you must format them before the conditional.
I think you are right about the formating. Now this is what Ive got, the raw input for the orderdate fields are numbers like, '234234' or an empty field is shown as 0 in the qlikview table viewer.
When I connect a master calendar to the dates it will automatically modify the this to dates like, '26-04-2010' or 0 is given as 31-12-71.
So I have to do something with formatting?
EDIT:
Many thanks! I fiddled arround a bit with your suggestions.
if(orderdate<>'0',date(orderdate+26298),'') AS OrderDate,
This seemed to work now. It was indeed a matter of finding the actual format. Thanks for the tip!