7 Replies Latest reply: Apr 15, 2013 6:44 PM by Kent Shook RSS

    Transform Null values from OUTER JOIN

      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_choicenameformatted
      1choice1"Choice 1."
      2choice2"Choice 2."
      3choice3"Choice 3."

       

      And we have the table SubChoice:

      id_subchoiceid_choicedetail
      11choice1_A
      21choice1_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_choiceid_subchoicenameformatteddetail
      11choice1"Choice 1."choice1_A
      12choice1"Choice 1."choice2_A
      2NULL  (but need to be -2) choice2"Choice 2."NULL  (but need to be NotApply)
      3NULL  (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!

        • Re: Transform Null values from OUTER JOIN
          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

            • Re: Transform Null values from OUTER JOIN

              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

                • Re: Transform Null values from OUTER JOIN
                  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

                    • Re: Transform Null values from OUTER JOIN

                      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

                        • Re: Transform Null values from OUTER JOIN
                          Jason Michaelides

                          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