empty missing

[victor_greffet]

Hey community,

Still stuck on that field :

if(len(trim(Release_DateBIS))=0,'empty',if(len(trim(Release_DateBIS))>10,date(right(Release_DateBIS,10)),date(Release_DateBIS))) as Release_Date,

I'm trying to do that :

• Release_DateBIS=0 --> 'empty'
• len(trim (Release_DateBIS)) > 10 --> date(right(Release_DateBIS,10))
• len(trim (Release_DateBIS)) = 10 --> Take Release_DateBIS

it almost works, i have blank cells instead of 'empty' when storing the Tab.

Do you have an idea ?

Thanks !

[YoussefBelloum]

Hi,

Try to attach sample data to see what you have exactly on those empty cells

[PrashantSangle]

do you have any value with length of that field <=9???

Regards,

[victor_greffet]

Hi, here is a sample of data

Ref	Release_DateBIS
1
2	12/01/2016
3	12/01/201612/02/2016
4
5	12/02/2016/12/03/201612/04/2016
6	12/04/2016
....

and I want :

Ref	Release_DateBIS
1	empty
2	12/01/2016
3	12/02/2016
4	empty
5	12/04/2016
6	12/04/2016
....	....
....	....
....	....

[jonathandienst]

What sort of field is Release_DateBIS? If it is a string, you should be using Date#() date interpretation function, rather than Date() date formatting function. Is right(Release_DateBIS,10)) a valid date that can be interpreted by QV?

Perhaps

If(Len(Trim(Release_DateBIS)) = 0, 'Empty',

If(Len(trim(Release_DateBIS)) > 10,

  Date#(Right(Release_DateBIS,10), '<format here>'),

  Date#(Release_DateBIS, '<format here>')

)

Insert the correct format string for the dates in <format here. For example.'yyyy/MM/dd' or 'dd-MM-yyyy' etc.

[victor_greffet]

Hi,

Values of length are respectively

• 0
• 10
• more than 10

(Cf above)

Regards

[victor_greffet]

Hi,

Thanks for your reply,

It works better but not fully ^^ I have empty but sometime blank, don't know why...

[victor_greffet · Accepted solution]

I found something that works but heavy in the script :

I did a left join creating a new field :

Left Join DATA:

Load

Ref,

if(len(trim([Release_Date]))=0,'empty',date([Release_Date])) as [Release_Date2]

Resident DATA;

Drop field Released_Date from DATA;

I split the calculation in two step