Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

victor_greffet
New Contributor III

empty missing

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 !



Tags (1)
1 Solution

Accepted Solutions
victor_greffet
New Contributor III

Re: empty missing

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

7 Replies
YoussefBelloum
Esteemed Contributor

Re: empty missing

Hi,

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

Re: empty missing

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

Regards,

Regards,
Prashant Sangle
victor_greffet
New Contributor III

Re: empty missing

Hi, here is a sample of data

RefRelease_DateBIS
1
212/01/2016
312/01/201612/02/2016
4
512/02/2016/12/03/201612/04/2016
612/04/2016
....

and I want :

RefRelease_DateBIS
1empty
212/01/2016
312/02/2016
4empty
512/04/2016
612/04/2016
........
........
........
MVP
MVP

Re: empty missing

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
New Contributor III

Re: empty missing

Hi,

Values of length are respectively

  • 0
  • 10
  • more than 10

(Cf above)

Regards

victor_greffet
New Contributor III

Re: empty missing

Hi,

Thanks for your reply,

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

victor_greffet
New Contributor III

Re: empty missing

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