topic Re: date(+value) Conditional in QlikView

date(+value) Conditional

Thu, 30 Apr 2015 14:50:24 GMT

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?

Re: date(+value) Conditional

Thu, 30 Apr 2015 15:03:53 GMT

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

Re: date(+value) Conditional

alex_millan — Thu, 30 Apr 2015 15:05:29 GMT

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,

Re: date(+value) Conditional

Thu, 30 Apr 2015 15:30:23 GMT

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?

Re: date(+value) Conditional

Thu, 30 Apr 2015 15:33:39 GMT

try this:

LOAD

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

date(del_date+26298) AS DelDate;

Re: date(+value) Conditional

alex_millan — Thu, 30 Apr 2015 15:34:21 GMT

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.

Re: date(+value) Conditional

Thu, 30 Apr 2015 15:49:50 GMT

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!

topic Re: date(+*value*) Conditional in QlikView

date(+*value*) Conditional

Re: date(+*value*) Conditional

Re: date(+*value*) Conditional

Re: date(+*value*) Conditional

Re: date(+*value*) Conditional

Re: date(+*value*) Conditional

Re: date(+*value*) Conditional

topic Re: date(+value) Conditional in QlikView

date(+value) Conditional

Re: date(+value) Conditional

Re: date(+value) Conditional

Re: date(+value) Conditional

Re: date(+value) Conditional

Re: date(+value) Conditional

Re: date(+value) Conditional