Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

date(+*value*) Conditional

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?

1 Solution

Accepted Solutions
Not applicable
Author

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!

View solution in original post

6 Replies
Not applicable
Author

If(len(orderdate)>0,date(orderdate+26298)) as OrderDate

alex_millan
Creator III
Creator III

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,

Not applicable
Author

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?

Not applicable
Author

try this:

LOAD

if(order date<>'30-12-99',date(orderdate+26298),'') AS OrderDate,

date(del_date+26298) AS DelDate;

alex_millan
Creator III
Creator III

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.

Not applicable
Author

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!