Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day,
I am trying to replicate the attached (Relational_Table.JPG) relational table in the script loader.
This is the code i have used in Qlik.
PRICING_StockManagment1:
buffer SQL SELECT `APS_eoq`,
`APS_ms`,
`APS_rol`,
stock_management.`st_mstockist`,
stock_management.`st_prodcode` as prodcode,
stock_management.`st_sdesc`
FROM pricing.`stock_management`
Left Join (aps.`item`, cstores.`stocktake`, central.`mvmnt`)
ON pricing.`stock_management`.`st_prodcode` = aps.`item`.`ST_Prodcode`
AND pricing.`stock_management`.`st_prodcode` = cstores.`stocktake`.`s_prodcode`
AND pricing.`stock_management`.`st_prodcode` IN (SELECT MV_PRODCODE
FROM central.`outstanding_stv`
INNER JOIN central.`mvmnt` on central.`mvmnt`.`MV_TRANNO` = central.`outstanding_stv`.`IS_INVNO`
WHERE R_BRANCH="APS")
WHERE `stock_management`.`APS_ms`>0
AND `stock_management`.`st_mstockist`="BRA"
OR `stock_management`.`st_mstockist`="FCS"
OR `stock_management`.`st_mstockist`="FKE"
;
APS_item1:
//APS query
SQL SELECT apsitem.`ST_Prodcode` as prodcode,
apsitem.`ST_SOH`
FROM aps.item as apsitem
LEFT JOIN iewkelvin.`item`
ON apsitem.`ST_Prodcode` = iewkelvin.item.ST_Prodcode
WHERE apsitem.`ST_Prodcode` IN (SELECT pricing.`stock_management`.`st_prodcode` FROM pricing.`stock_management`)
;
FKE_item_1:
//FKE query
SQL SELECT fkeitem.`ST_Prodcode` as prodcode,
fkeitem.`ST_SOH`
FROM iewkelvin.item as fkeitem
left Join cstores.`item`
ON fkeitem.`ST_Prodcode` = cstores.`item`.`ST_Prodcode`
WHERE fkeitem.`ST_Prodcode` IN (SELECT aps.`item`.`ST_Prodcode` FROM aps.`item`)
;
FCS_iteme1_2:
//FCS query
SQL SELECT csitem.`ST_Prodcode` as prodcode,
csitem.`ST_SOH` as csSOH
FROM cstores.item as csitem
WHERE csitem.`ST_Prodcode` IN (SELECT iewkelvin.`item`.`ST_Prodcode` FROM iewkelvin.`item`)
;
FCSpn:
//FCS PageNo Query
SQL Select S_PageNo,
S_ProdCode AS prodcode
FROM cstores.stocktake as csstocktake
LEFT JOIN cstores.`sheets` ON csstocktake.`S_ProdCode` = cstores.`sheets`.`PageNo`
WHERE csstocktake.`S_ProdCode` IN (SELECT pricing.`stock_management`.`st_prodcode` FROM pricing.`stock_management`)
;
FRC:
//FRC PageNo query
SQL Select LocationDescrip,
PageNo AS S_PageNo
FROM cstores.sheets
WHERE sheets.PageNo IN (select cstores.stocktake.S_PageNo from cstores.stocktake)
;
CENTRAL:
//Central Query
SQL SELECT MV_PRODCODE as prodcode, SUM(MV_Qty) AS TotalMVQ
FROM central.outstanding_stv
inner JOIN central.`mvmnt` on MV_TRANNO=outstanding_stv.IS_INVNO
LEFT JOIN pricing.`stock_management` ON mvmnt.`MV_PRODCODE`= pricing.`stock_management`.`st_prodcode`
WHERE R_BRANCH="APS" group by MV_PRODCODE
;
This code produces the following QlikView table view (QV_TableView.jpg) which does not look correct but im not sure how to go about sorting this our if im even in the right direction.
Thank you for any help offered.
I guess you are confused by the look of QlikView data model. Ctrl + T to see the table viewer:
If you pick "Source Table View", it might look similar to your original data model.
Hope this is what you were looking for.
Looks ok to me. What is wrong with it?
I guess you are confused by the look of QlikView data model. Ctrl + T to see the table viewer:
If you pick "Source Table View", it might look similar to your original data model.
Hope this is what you were looking for.
Confusion might just be it as i expected the same layout.
Source Table view doesn't make a difference though.
Thanks Sinan