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

[#1]

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?

[Gysbert_Wassenaar]

No idea. Works for me... after fixing the table references in the resident loads.

[#3]

Gysbert, thanks for replying, if pivoting from NewJoin table instead of inventory columns(FGQty, FGIQty etc.) are null

I am trying to understand,

1)When third load is from 'Inventory table' :-

that after the left join, Inventory table shows data for Data type 'Sample' appended to it(i believe data from first and second load is concatenated as there is no Noconcatenate used) then why after third load from 'Inventory' table it doesn't show values for columns specifically(Best Case Qty, Pipeline Qty, CommitQty) from second load table.

If you see Inventory table in table box you can see the appended data.

2)When third load is from 'New Join' table:-

why inventory specific columns(FGQty, FGIQty etc.) are null.

[Gysbert_Wassenaar]

I think your mistaking data model tables and table box objects. If you add a field to a table box then all the values in that field are shown regardless of the data model table the values come from. The Sample values are not appended, but come from another table than the other values.

[#5]

Got it!

I used exists instead join, as I do not want intermediate join table. In my scenario every query data set is concatenated into one table.

From the second query I only wanted records that matched the keys of fist query. Thats why was trying left join with query1

MainTable:

Load product, col1, col2;

select query1;

Concatenate(MainTable)

Load *

where exists(product, product);

select product, col1, col3 etc query2;

Concatenate(MainTable)

Load*;

select query3;

and so on..........