topic Re: empty missing in App Development

empty missing

victor_greffet — Mon, 20 Nov 2017 09:39:11 GMT

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 !

Re: empty missing

YoussefBelloum — Mon, 20 Nov 2017 09:42:47 GMT

Hi,

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

Re: empty missing

PrashantSangle — Mon, 20 Nov 2017 09:47:01 GMT

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

Regards,

Re: empty missing

victor_greffet — Mon, 20 Nov 2017 09:50:43 GMT

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
....	....
....	....
....	....

Re: empty missing

jonathandienst — Mon, 20 Nov 2017 09:52:17 GMT

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.

Re: empty missing

victor_greffet — Mon, 20 Nov 2017 09:52:32 GMT

Hi,

Values of length are respectively

0
10
more than 10

(Cf above)

Regards

Re: empty missing

victor_greffet — Mon, 20 Nov 2017 09:59:27 GMT

Hi,

Thanks for your reply,

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

Re: empty missing

victor_greffet — Mon, 20 Nov 2017 10:10:21 GMT

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