Identify and select Not Null but blank values

[Daniel77]

Hi all,

I have a table where i have included a dimension (Date Ordered) to try to display only those with no Date Ordered. see a snippet below:

The '-' is not a true null as cannot be identified with a flag (if isnull(Date ordered),'y','n')

For this i only get the n where the date is and nothing in the value with'-'.

In this table i need to only show the values with no Date Ordered. Can anyone share their experience on how to null the date value, remove and only leave those with '-', which i would like to name as 'Not Ordered'

I have tried "if (match(Date Ordered,'-'" and ' ' but nothing i try gives the '-' a value.

Thank you so much in advance.

Happy New Year Qlikers!

Daniel

[Or]

This is a true null - you can tell because if it was a value, it wouldn't have a gray background.

An easy way to work around nulls in this scenario is to use len(Field)=0, which technically also picks up empty strings but you probably don't have any of those in your date field.

[anat]

can you try like below :

if(len(trim(field))=0  or field='-' or isnull(field),'Not ordered',field)

[Daniel77]

Hi Anat,

Thank you do much for the reply. these are the type of expression tried. I took your advice and went for:

=if(len(trim([Prod Location]))=0,'not Ordered','ordered')

the result is the same with value for.

Thank you for the suggestion. any other ide would be great.

Daniel

[Daniel77]

Hi,

I tried this, thank you for the suggestion:

=if(len([Prod Location] =0),'y','n')

I still only get a value for the ordered value and not for the un ordered which is -

Appreciate the suggestion and you time. This is odd, i thought i would be able to do a simple,

If(isnull(Prod Location),'y','n') but nothing i try is giving the 'null' a value to filter.

Thank you again for the suggestions.

Daniel

[Daniel77]

Hi,

See below:

=if(isnull([Prod Location]), 'y',[Prod Location])

With this is only see the prod location and no 'y'

field name is irrelevant (Date Ordered ), i am using the above expression.

The data in the Prod location comes from a different table linked by [Purchase Order No] in column 2.

Thanks again, if you have any other comment i would appreciate. No sure why this is not working as expected.

Daniel

[marcus_sommer]

NULL means there is NOTHING to exists. It won't be stored and isn't accessible in any way.

Within the script you may try to replace them with approaches like the above suggested ones, for example:

if(isnull(Field), 'NULL', Field) as Field // worked only by real NULL
if(len(trim(Field)), Field, 'NULL') as Field

But if the NULL isn't in the load else is caused from a join you couldn't grab it directly  else you need a following load to check it.

If the join didn't happens in the script else within the data-model by associating tables you couldn't replace it in any way - but you may hide it. Just by enabling the appropriate object-properties.

Very helpful might be also to check the tables/data against each other and populating missing ones.

Further methods to handle NULL's might be the usage of NULL variables or a mapping. Beside this NULL is not mandatory an error else it could provide more insights as the replace/hiding approaches.

[Daniel77]

Hi Marcus

Thank you so much for the insight into Null, i am not sure what i will do yet but really appreciate your time, expertise and willingness to assist.

Thanks

Daniel