Replace Null value with Placeholder Text

[JustinDallas]

Hello Everyone,

I have a data model where I have "Workers" who have records in a "PayDetail" table. This "PayDetail" table also has a linking to an "Order" table.  HOWEVER, not every PayDetail has an associated Order (i.e you pay someone for undergoing HR training or recertification). 

I'm representing this data in a Pivot table, and because of the structure, I end up with pay columns that have a "-" as their Order Number.

Is there a way I can say "if the OrderNumber is null then show Blah"?  I've tried the following statement, but it didn't change anything:

IF(ISNULL([Order Number]),'OTHER',[Order Number])

Any help is greatly appreciated.

[Anonymous]

could be missing values, in QlikView you can change the representation for missing

--> look for missing symbol

Don't know if it is possibe in QlikSense...

you could also try

IF(len(trim([Order Number]))=0,'OTHER',[Order Number]) 

[JustinDallas]

Hello Robin,

I tried the last statement that you put down, and it still didn't seem to care. 

[Anonymous]

... I still suppose the values are missing instead of being null() .

I don't know if you can change the representation of missing in QlikSense.

A workaound could be building all non existing  dimension combinations in script...

then they are null() instead of missing I think ...

[sasiparupudi1]

Try

Alt (Ordernumber,'Not valid')

[Anonymous]

I think this won't work, because the dimensions just don't exist for missing values and there will be no calculation...

(could be that I'm wrong )

perhaps there'a an option like that in QlikSense?!?:

[sasiparupudi1]

If (aggr (count (ordernumber),empid,date)=0,'na',ordernumber)

[CarlosAMonroy]

It may actually be a hyphen.

You can try following:

IF(ISNULL([Order Number]) or [Order Number] ='-' or len([Order Number])=0 or [Order Number]='','OTHER',[Order Number])

Hope that helps,

Carlos M

[eniemenm]

This seems to be working:

=aggr(if(isnull([Order Number]),'Other',[Order Number]),[Order Number])

[beck_bakytbek]

Hi Justin,

look at this: https://www.youtube.com/watch?v=pGpdfMpmWoE

i hope that helps to resolve your issue

Beck