if

[raadwiptec]

hi

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

[susovan]

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])))

[raadwiptec]

hi peter,, 1 is fair good. still testing

[#4]

Hi,

I think that the possible solution is this :

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

Bye

[Anonymous]

Hi

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

[raadwiptec]

hi thomas

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

[Anonymous]

Ok.

Then try

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

That returns 0 for valid an -1 for invalid dates

[raadwiptec]

hi thomas in your expression what is 20160917?

[Anonymous]

‌It's a date.

Year 2016

Month 09

Day 17

[raadwiptec]

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

[Anonymous]

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)