NullAsValue or IF(ISNULL()) not working

[Pico]

Hi all,

I'm trying to see '0' instead of the Qlik null in a field. 

Now, I have a huge dimension table, with, among others, the column IDs.

I'm uploading this new table (aka ServiceTable) with the following columns:

IDs	UerdID	Service
00001234	Mark_00	blalba
00001235	John_02	carcar

Now, not all the IDs in the dimension table are present in the ServiceTable, hence, if I want to create the table with the following columns, this happens:

IDs	Uses a service?
00001234	YES
00000022	-
00000333	-

I would like to have values in the right column (like 'NO'/'YES', trivially)

I tried two solutions:

1) In the Data load Editor:

NullASValue FLGSERVICE;
set NullAsValue = 0;

ServiceTable:
SQL
select
[fields]
1 as FLGSERVICE
from [myDB]
where 1=1
and [trivial checks]
;

2) In the Table

=if(isnull(FLGSERVICE),'NO', 'YES')

None of the worked. what can I do?

I cannot modify the loading process of the huge dimension table, so I can modify only the loading process of the ServiceTable

Thank you very much

Pico

[BrunPierre]

Instead of isnull, you may want to try this instead

If(Len(Trim(FLGSERVICE))= 0,'NO', 'YES')

[Pico]

Thank you for the answer, I have to be more precise with my question:

I would like to let the user select the fields 'YES' or 'NO', using a Dimension Column.

The solution "If(Len(Trim(FLGSERVICE))= 0,'NO', 'YES')" works well with measure column but doesn't allow the user to select the values, so is not what I was looking for.

Trying this solution on a dimension column, I got the null values as before

[MayilVahanan · Accepted solution]

Hi

Try with Mapping table in the script

MapFlag:

Mapping Load IDs, FLGSERVICE
select
IDs
'Yes' as FLGSERVICE

from [myDB]
where 1=1
and [trivial checks]
;

Load *, ApplyMap('MapFlag', IDs, 'No') as FLGSERVICE from urdimensiontable;

In front end, hope it will display either Yes or No.

[Pico]

That would be great, and theoretically speaking it should work fine, but it modifies the dimension table loading process, which is kind of a problem. 

[MayilVahanan]

Hi,

Hope, Mapping table loads faster