Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Relational Tables & Table View

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.

1 Solution

Accepted Solutions
sinanozdemir
Specialist III
Specialist III

I guess you are confused by the look of QlikView data model. Ctrl + T to see the table viewer:

Capture.PNG

If you pick "Source Table View", it might look similar to your original data model.

Hope this is what you were looking for.

View solution in original post

3 Replies
Gysbert_Wassenaar

Looks ok to me. What is wrong with it?


talk is cheap, supply exceeds demand
sinanozdemir
Specialist III
Specialist III

I guess you are confused by the look of QlikView data model. Ctrl + T to see the table viewer:

Capture.PNG

If you pick "Source Table View", it might look similar to your original data model.

Hope this is what you were looking for.

Not applicable
Author

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

Source Table view doesn't make a difference though.

Thanks Sinan