Len function returning values greater than 0 for null fields

[CheeseMan84]

I am attempting to create a field in Qlik to flag null values for 2 fields. The name of the fields that contain the nulls are called 'RealizedArrivalDateTime' and 'RealizedDepartureDateTime'

the line of code in the Data Load Editor is as follows

If(len(trim([realizedArrivalDateTime])) <> 0 OR len(trim([realizedDepartureDateTime])) <> 0, 'Not Missing', 'Missing') as [Arrival_Departure_Flag],

After loading this new field, i noticed every record was being tagged as 'Not Missing' even where value in realizedarrival and realizeddeparture was null.  As a test i added this code into the Data Load Editor

len(trim([realizedArrivalDateTime])) as [Arrival Length],
len(trim([realizedDepartureDateTime])) as [Departure_Length]

When i loaded these fields and built a small table to check, here were my results:

Anyone have any idea why these nulls have varying lengths greater than 0? Any other ideas as to how to key in on these values?

Thanks in advance

[NellyAcko]

Hi try this the other way round:so all 

If(len(trim([realizedArrivalDateTime])) > 0 OR len(trim([realizedDepartureDateTime])) > 0, 'Missing', 'Not Missing') as [Arrival_Departure_Flag],

Also if tis does not work can you supply an example of the datetime field as there is other options for the if statement.

[CheeseMan84]

Unfortunately that statement did not work, because the null values are returning len values >0, every field shows as not missing. Below are some examples of the date time field i am using

[CheeseMan84]

@NellyAcko 

[anat]

Can you try like suppose your date field expected leng 24 digits

If(len(trim(field) )='24', 'not missing', ' missing') 

[NellyAcko]

OK so replicating your data you need to load the date fields and let QLIK know how this field is coded this is done one way as below: 

1st of all the load lets QLIK read the date-time, once this is loaded you reload this data with your expression included which will now work as the fields are correctly identified as DateTime. Finally you drop the original load leaving you with  the desired results. 

TEST:
LOAD
Timestamp#(realizedArrivalDeteTime, 'd/m/yyyy h:mm:ss tt') as realizedArrivalDeteTime,
Timestamp#(realizedDepartureDateTime, 'd/m/yyyy h:mm:ss tt') as realizedDepartureDateTime,
id_shift,
subOrderCode
FROM [lib://AttachedFiles/QLIK TEST DATES.txt]
(txt, utf8, embedded labels, delimiter is ',', msq);

NoConcatenate
LOAD
Timestamp#(realizedArrivalDeteTime, 'd/m/yyyy h:mm:ss tt') as realizedArrivalDeteTime,
Timestamp#(realizedDepartureDateTime, 'd/m/yyyy h:mm:ss tt') as realizedDepartureDateTime,
id_shift,
subOrderCode,
if(len(trim([realizedArrivalDeteTime])) <> 0 OR len(trim([realizedDepartureDateTime])) <> 0, 'Not Missing', 'Missing') as [Arrival_Departure_Flag]
Resident TEST ;

Drop table TEST;

[CheeseMan84]

Receiving the following error:

[vinieme12]

You original script is actually working,

but the table chart is showing you unique combinations of all "Not Missing" flags in the dataset and NOT EACH DATA ROW

you can verify this by adding a recno() while loading the data

Load

Recno() as recordID

, If(len(trim([realizedArrivalDateTime])) <> 0 OR len(trim([realizedDepartureDateTime])) <> 0, 'Not Missing', 'Missing') as [Arrival_Departure_Flag]

,len(trim([realizedArrivalDateTime])) as [Arrival Length],
,len(trim([realizedDepartureDateTime])) as [Departure_Length]

From XyzSource;

then add RecordID field to the table to verify each data row

alternatively you can also try below condition

 if(len(KeepChar([realizedArrivalDateTime],'0123456789'))>1 or len(KeepChar([realizedDepartureDateTime],'0123456789'))>1 , 'NotMissing','Missing') as Arrival_Departure_Flag