4 Replies Latest reply: Apr 15, 2015 5:51 PM by neha mishra RSS

    join shows values in resultant table but afterwards while pivoting the data through if condition values from second table are -

      Hello experts, I am new to qlik view and trying to understand basics here.

      I have a ques.

       

      I have to join two tables, Inventory and sample,

      * based upon the common product id column.

      * there are three other columns which have common names in both the tables

      * and then need to pivot data in final table.


      My qvw editor have three tabs -


      (TAB 1): First table


      Inventory:

      LOAD * INLINE [

      DATA_TYPE, DATA_GRP, PRODUCT_ID, QTY, BUYER

      'INV',                  FG        ,      3200,            3,           A

      'INV',                  FGI       ,      3200,             2,          A

      'INV',                 STG       ,       3200,           3,           Y

      'INV',                   FG        ,     2200,            7,           G

      'INV',                  FGI       ,       2200,           4,           F

      'INV',                  STG       ,      2200,           6,           A

      'DEMAND',         IN        ,       3200,            3,           A

      'DEMAND',        OM       ,        3200,           2,           A

      'DEMAND',        IN        ,        2200,            7,           G

      'DEMAND',         OM       ,       2200,           4,           F

      ];

       

      (TAB 2): Second table


      NewJoinTable:

      load PRODUCT_ID     // common columns data for base table will be fetched even if join condition is on one column. The data will                                             // eventually be concatenated to Inventory as NoConcatenate is not being used

      resident INV;

      left join

      LOAD * INLINE [

        DATA_TYPE, DATA_GRP, PRODUCT_ID, QTY

        'SAMPLE', PIPELINE     ,3200,  1

        'SAMPLE', COMMIT       ,3200,     1

        'SAMPLE', BESTCASE     ,3200,     1

        'SAMPLE', PIPELINE     ,2200,     1

        'SAMPLE', COMMIT       ,2200,  1

        'SAMPLE', BESTCASE     ,2200,  1

        'SAMPLE', PIPELINE     ,7777,     1

        'SAMPLE', COMMIT       ,7777,  1

        'SAMPLE', BESTCASE     ,7777,  1

        'SAMPLE', PIPELINE     ,6999,     1

        'SAMPLE', COMMIT       ,6999,  1

        'SAMPLE', BESTCASE     ,6999,  1

        'SAMPLE', PIPELINE     ,7200,     1

        'SAMPLE', COMMIT       ,7200,  1

        'SAMPLE', BESTCASE     ,7200,  1

         ]

      ;

       

      (TAB 3) :

      FACT:

      LOAD   * 

           ,if(DATA_GRP = 'IN', QTY) as INQty  

          ,if(DATA_GRP = 'OM', QTY) as OMQty 

         ,if(DATA_GRP = 'FG', QTY) as FGQty      

          ,if(DATA_GRP = 'FGI', QTY) as FGIQty  

          ,if(DATA_GRP = 'STG', QTY) as STGDQty    

          ,if(DATA_GRP = 'BESTCASE', QTY) as BestCaseQty  

          ,if(DATA_GRP = 'COMMIT', QTY) as CommitQty       

          ,if(DATA_GRP = 'PIPELINE', QTY) as PipelineQty  

      Resident newfact;

       

       

      When I viewed the data of table FACT using a table box, column DATA_GRP entries from second joined table were null ( - )

      Why is it so?