Transform Null values from OUTER JOIN

[#1]

Hi guys,

I'm still learning how to do some simple tasks on Qlikview, and searching for the same problem that I have, but I didn't find it solved.

Well, my problem consist, like the subject suggest, in transform the null value of an OUTER JOIN group on Qlikview to another value... like 'NotApply' when the type of the field is a character, and -2 when the type of the field are an integer...

In the example, we have the table

Choice:

id_choice	name	formatted
1	choice1	"Choice 1."
2	choice2	"Choice 2."
3	choice3	"Choice 3."

And we have the table SubChoice:

id_subchoice	id_choice	detail
1	1	choice1_A
2	1	choice1_B

I only learned how to make the OUTER JOIN:

choices:
load id_choice,
       name, 
       formatted;
sql select c.id AS id_choice, 
               c.name, 
               c.formatted
from db.schema.choices c;
subchoice:
outer join (choice)
SQL SELECT s.id as id_subchoice,
                     s.id_choice,
                     s.detail
FROM db.schema."sub_choice";           

But, the results of this OUTER JOIN are like this table:

id_choice	id_subchoice	name	formatted	detail
1	1	choice1	"Choice 1."	choice1_A
1	2	choice1	"Choice 1."	choice2_A
2	NULL  (but need to be -2)	choice2	"Choice 2."	NULL  (but need to be NotApply)
3	NULL  (but need to be -2)	choice3	"Choice 3."	NULL  (but need to be NotApply)

Usually, this kind of result may work fine in some if the field choose as filter by the client are the name of the choice... but this don't works when the client choose the details as filter... and the source of the data of this Qlikview are a datamart that transform null values using some constants, and in that case, use NotApply to text fields, and -2 to integer fields.

So I ask if there is a way to transform that null values resulted by the OUTER JOIN into values 'NotApply' and -2 in the appropriate field...

Please, I know that with SQL I can do it... but how I said... I'm learning Qlikview, so I'm trying to use the tools of Qlikview wherever it's possible...

Thanks!

[Jason_Michaelides]

Dealing with Nulls in QlikView takes some practice so don't think you're asking a simple question!  You need to understand 3 NULL situations:

1. The data you are loading has NULL values in it already

2. NULLs are created by a scripted join like your situation above

3. NULLs are present due to the QlikView joins between tables. E.g. Your Customer table has 10 customers in it but your sales table only has rows that link to 9 of them (ie one customer has no sales). If you were to create a table object of Customers and all sales rows there would be Null sales values next to the 10th customer details.

The first 2 of these can be replaced in the script (and I almost always do this) so that they are properly selectable in the charts etc. Situation 3 just needs to be carefully managed!

Some further info on each situation:

1. NULLs are loaded as NULLs and can therefore be replaced as they are being loaded.

2. NULLs are created by the join. This means they don't exist until the join is finished, so a further step is needed to deal with them.

There are several functions such as NullAsValue() (look them up in the F1 help) but my preferred method is MAP....USING. This allows a lot of control and is generally pretty efficient as it only replaces the value once in the script - at the end just before the field is stored. First, create a mapping table(s) in your script:

Map_Null1:

MAPPING LOAD

   Null(),

   -2

Autogenerate 1;

Map_Null2:

MAPPING LOAD

    Null(),

    'NotApply'

Autogenerate 1;

//now decide what map to use where

MAP id_subchoice USING Map_Null1;

MAP detail USING Map_Null2;

//now load your data

Data:

Load blah blah blah FROM...;

If either of your fields were loaded directly as null then that's all you have to do. However, your nulls are created via a join so you need to force another load of the fields in question. If these fields are transformed or loaded again from a resident load later in the script then don't do anything else. However, if your fields are now finished with then you must force another load. I use an inner join of the table on itself. Makes no difference and is quick:

INNER JOIN (Data) LOAD * RESIDENT Data;

That should do it!

Hope this helps,

Jason

[#3]

Thank you Jason,

On my Datamart that are the source of my data... already there are treatments applied by the ETL tool, that translate the 1st null condition... so in all my dimentions there're mapped constants that translate these condition represented in each type of data (char, date, integer). So do the condition when we already knew that the data NotApply for any motive...

So, I need the similar in Qlikview, dealing with OUTER JOIN, transforming the null resulted by these Outer Join to NotApply, because in some situation, we know there aren't any data to be drilled, but it's necessary to show it... and I know that Qlikview don't show Null values on Lists to be selected as dashboard's filters...

I will test this Mapping load suggested... but I'm not sure if I understand how to use this...  can I use this in de Load of the OUTER JOIN table?

Thanks for your help,

Carlos

[Jason_Michaelides]

Copy and paste this script:

Map_Null1:

MAPPING LOAD

   Null(),

   -2

Autogenerate 1;

Map_Null2:

MAPPING LOAD

    Null(),

    'NotApply'

Autogenerate 1;

//now decide what map to use where

MAP id_subchoice USING Map_Null1;

MAP detail USING Map_Null2;

//now load your data

choices:

load

     id_choice,

     ,name

     ,formatted

;

sql select c.id AS id_choice,

               c.name,

               c.formatted

from db.schema.choices c;

outer join (choices)

LOAD

     id_choice

     ,id_choice

     ,detail

;

SQL SELECT s.id as id_subchoice,

                     s.id_choice,

                     s.detail

FROM db.schema."sub_choice"; 

//Now force the reload to apply the null mapping

INNER JOIN (choices) LOAD * RESIDENT choices;

Hope this helps,

Jason

[#5]

Jason,

Thanks for the script... but I think that still there are any error on it...

When I follow strictly (only adjusting the id_subchoice column that I think you forgot on joined table) ... I got as result something like a SQL's INNER JOIN between choice and subchoice... so, only registers of choice that there are subchoices related.

Although, when I remove the INNER JOIN of the last LOAD... maintaining the last LOAD to apply the map... I finally can see the transformation applied... but... as result I got a table with that transformation, plus the same registers transformated showing the NULL value...

In other words, I get the registers duplicated... 😕

There are another thing that you think that works in this situation?

Here the code I used to test this...

Map_Null1:
MAPPING LOAD
   Null(),
   -2
Autogenerate 1;
Map_Null2:
MAPPING LOAD
    Null(),
    'NotApply'
Autogenerate 1;
//now decide what map to use where
MAP id_subchoice USING Map_Null1;
MAP detail USING Map_Null2;
//now load your data
choices:
LOAD * 
inline [               id_choice  , name          , formatted
                               1     , 'choice1'      , 'Choice 1.'
                              2      , 'choice2'      , 'Choice 2.'
                              3      , 'choice3'      , 'Choice 3.' ]
; 
outer join (choices)
LOAD *
inline [ id_subchoice , id_choice      , detail 
                   1       , 1                  , 'choice1_A'           
                      2       , 1                  , 'choice1_B' ];
//Now force the reload to apply the null mapping
LOAD id_choice,
           id_subchoice,
           name,
           formatted,
           detail
 RESIDENT choices;

Ps.: I tryed to use ApplyMap on last Load... without the MAP of the beginner... but I had the same results...

Thanks,

Carlos Figueiredo

[Jason_Michaelides · Accepted solution]

OK - I really don't know why the INNER JOIN is not working here.  But that may be because I'm drunk...

Try:

Map_Null1:

MAPPING LOAD

   Null(),

   -2

Autogenerate 1;

Map_Null2:

MAPPING LOAD

    Null(),

    'NotApply'

Autogenerate 1;

//now decide what map to use where

MAP id_subchoice USING Map_Null1;

MAP detail USING Map_Null2;

//now load your data

choices:

LOAD * inline [

          id_choice,name,formatted

          1,'choice1','Choice 1.'

          2,'choice2','Choice 2.'

          3,'choice3','Choice 3.'

]

;

outer join (choices)

LOAD * inline [

          id_choice,id_subchoice,detail

          1,1,'choice1_A'

          1,2,'choice1_B'

];

Final:

NoConcatenate

LOAD * RESIDENT choices;

DROP TABLE choices;

This seems to work,

Jason

[#7]

IT'S ALIVE!!!!!

Hey bro!!! It works very nice now!!!

If I could, I paid you a beer!!!

Thanks,

Carlos

[Anonymous]

The Inner Join wasn't working because the whole point of doing this was to get the null values to map, but when you do an inner join, the null values are removed anyway...

Or, it could've been the booze.