topic Re: Len function returning values greater than 0 for null fields in App Development

Len function returning values greater than 0 for null fields

CheeseMan84 — Sat, 03 Feb 2024 21:44:43 GMT

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

Re: Len function returning values greater than 0 for null fields

NellyAcko — Sat, 03 Feb 2024 23:45:10 GMT

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.

Re: Len function returning values greater than 0 for null fields

CheeseMan84 — Sun, 04 Feb 2024 14:00:01 GMT

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

Re: Len function returning values greater than 0 for null fields

CheeseMan84 — Sun, 04 Feb 2024 14:00:57 GMT

@NellyAcko

Re: Len function returning values greater than 0 for null fields

anat — Sun, 04 Feb 2024 14:37:07 GMT

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

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

Re: Len function returning values greater than 0 for null fields

NellyAcko — Sun, 04 Feb 2024 18:34:26 GMT

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;

Re: Len function returning values greater than 0 for null fields

CheeseMan84 — Mon, 05 Feb 2024 04:42:13 GMT

Receiving the following error:

Re: Len function returning values greater than 0 for null fields

vinieme12 — Mon, 05 Feb 2024 08:25:12 GMT

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