Relational Tables & Table View

Thu, 02 Jul 2015 15:19:25 GMT

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.

Re: Relational Tables & Table View

Gysbert_Wassenaar — Thu, 02 Jul 2015 15:25:14 GMT

Looks ok to me. What is wrong with it?

Re: Relational Tables & Table View

sinanozdemir — Thu, 02 Jul 2015 15:57:16 GMT

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.

Re: Relational Tables & Table View

Thu, 02 Jul 2015 16:31:44 GMT

Confusion might just be it as i expected the same layout.

Source Table view doesn't make a difference though.

Thanks Sinan

topic Re: Relational Tables & Table View in QlikView

Relational Tables & Table View

Re: Relational Tables & Table View

Re: Relational Tables & Table View

Re: Relational Tables & Table View