Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
No idea. Works for me... after fixing the table references in the resident loads.
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.
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.
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..........