topic Re: if in QlikView

if

raadwiptec — Wed, 25 Nov 2020 16:16:04 GMT

i have the following required in expression.. i have 3 columns

closing date

last date

opening date

My condition is as below

if closing date is blank or null the take the date from opening date column ,if the opening date is null or blank then take the date from last date column

Re: if

sunny_talwar — Tue, 28 Jun 2016 11:34:03 GMT

May be like this:

If(Len(Trim([Closing Date])) > 0, [Closing Date],

If(Len(Trim([Opening Date])) > 0, [Opening Date], [Last Date]))

Re: if

raadwiptec — Tue, 28 Jun 2016 11:52:51 GMT

hi sunny,

len is not working as many of the data is null or -

Re: if

jayaseelan — Tue, 28 Jun 2016 11:56:49 GMT

Hi,

Try This..

if(GetSelectedCount([closing date])=1,[Closing Date],

if(GetSelectedCount([Opening Date])=1,[Opening Date], [Last Date]))

Re: if

antoniotiman — Tue, 28 Jun 2016 11:59:36 GMT

If(Len(Trim([Closing date]))=0,If(Len(Trim([opening date])) > 0,[Opening date],[Last date]),[Closing date])

Re: if

Peter_Cammaert — Tue, 28 Jun 2016 12:17:04 GMT

That's exactly why Sunny is suggesting to use the len(trim()) combo. It handlles empty fields, whitespace strings, Null values all in the same way. This is a best practice if you cannot expect the data source to return a single "no value present" indicator.

Re: if

raadwiptec — Tue, 28 Jun 2016 12:20:26 GMT

hi jayasselan ,

this is giving results but it only takes Last Date values......it is not considering even if opening dates are there

Re: if

Tue, 28 Jun 2016 12:24:03 GMT

can you provide screenshot of the data?

Re: if

raadwiptec — Tue, 28 Jun 2016 12:24:24 GMT

hi peter,

If lenght is used .. i dont get any values.. not sure why.. but for example if i put

if (closing date) = '' or '-' ,date(opening date),-- it correctly takes from other column.. but if i put one if condition it does not work

Re: if

Peter_Cammaert — Tue, 28 Jun 2016 12:31:22 GMT

So this means that the hyphen is actually present in your date fields? And not just the representation of a Null value in a QlikView object? You may want to change the zeroes in Sunny's expression into 1s.

If this still doesn't work, then we are lacking context details. Please upload an example document that exhibits this behavior. Thanks.

Peter

Re: if

susovan — Tue, 28 Jun 2016 12:33:39 GMT

Hi,

You also try this expression,

if([Closing Date]<[Last Date],[Last Date],if([Last Date]<[Opening Date],[Opening Date],if([Opening Date]<[Closing Date],[Closing Date])))

Re: if

raadwiptec — Tue, 28 Jun 2016 12:47:28 GMT

hi peter,, 1 is fair good. still testing

Re: if

Tue, 28 Jun 2016 13:35:37 GMT

Hi,

I think that the possible solution is this :

IF(Closing_Date = '' , Opening_Date , IF(Opening_Date = '' , Last_Date , Closing_Date))

Bye

Re: if

Anonymous — Sat, 02 Jul 2016 07:00:26 GMT

Use the isnull() function and combine it with the [field] = '' and you'll get the result. Remember that working with null values in your data makes it tricky. You should handle them in your load script

Use this:

if(isnull(Closing_Date)=-1 or Closing_Date='',if(isnull(Opening_Date)=-1 or Opening_Date='',Last_Date,Opening_Date),Closing_Date)

Have a nice day

Br Thomas

Re: if

raadwiptec — Mon, 04 Jul 2016 06:29:43 GMT

hi thomas

the problem is the value is not null.. but something like '/'

Re: if

Anonymous — Mon, 04 Jul 2016 06:41:52 GMT

Ok.

Then try

isnull(date(date#(20160917,'YYYYMMDD'),'YYYY-MM-DD'))

That returns 0 for valid an -1 for invalid dates

Re: if

raadwiptec — Mon, 04 Jul 2016 06:48:58 GMT

hi thomas in your expression what is 20160917?

Re: if

Anonymous — Mon, 04 Jul 2016 06:58:57 GMT

‌It's a date.

Year 2016

Month 09

Day 17

Re: if

raadwiptec — Mon, 04 Jul 2016 07:09:54 GMT

my curent issue is my data looks as below after the expression. Once the closing date is populated..it creates a new line..showing it as a duplicate value in the report..

Country	Printer Type	Code	Status	destination Country	Quantity	Closing Date	Opening Date	Last Date
uk	hp-printer0323	14CC023	Delivered	sweden	1	09/05/2015	09/05/2015	/
uk	hp-printer0323	14CC023	Delivered	sweden	1	-	09/05/2015	28/05/2015

Re: if

Anonymous — Mon, 04 Jul 2016 09:33:03 GMT

Ok, then use the following in the load script:

,date(date#(Closing_Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Closing_Date

,date(date#(Opening_Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Opening_Date

,date(date#(Last_Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Last_Date

And then use this code on the fields:

if(isnull(Closing_Date)=-1,if(isnull(Opening_Date)=-1,Last_Date,Opening_Date),Closing_Date)