Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: date(+*value*) 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!

6 Replies
Not applicable

Re: date(+*value*) Conditional

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

alex_millan
Contributor III

Re: date(+*value*) Conditional

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

Re: date(+*value*) Conditional

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

Re: date(+*value*) Conditional

try this:

LOAD

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

date(del_date+26298) AS DelDate;

alex_millan
Contributor III

Re: date(+*value*) Conditional

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

Re: date(+*value*) 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!

Community Browser