How to restrict product from one table.

[suvechha_b]

Hi,

I want to  the product in the "FactDistributor "

which are already in "Supplier Order".

I dont want extract product to be loaded.

Please find my sample model attached.

[Gysbert_Wassenaar]

If you want to remove the records related to the products that do not exist in the table factDistributor then add a right keep before the load statement for factDistributor:

right keep

"factDistributor":
select
product_key
,qty_available as StockAvailable
,qty_blocked_qc as StockBlocked
,reason_code
,reason_code_desc
from JNBVDW02A.edw.dbo.qlk_fact_distributor_stock
where
reason_code in (80,81,85);

[suvechha_b]

Please see my full script :

"SupplierOrders":
SELECT
division_key,
product_key,
customer_key,  
order_period_key,
order_date_key,
planned_delivery_period_key,
planned_delivery_date_key,
order_status_key,
order_type_key,
[Order Number],
[Customer Reference],
[Invoice Number],
invoice_date_key,
[Order Qty],
[Delivered Qty],
[Picked Qty],
[OutStanding Qty],
nsn,
contract_no,
tender_no,
doh_description,
doh_brand_name,     
supplier_name,
contract_start_date,
contract_end_date,
"Supplier Order Receipt date",
"Date Order Expected By Depot",
"Depot Order Date",
"comment",
"order_fulfilled",
FROM edw.dbo."qlk_fact_sales_orders";




"qlkinvoicedate":
Load "invoice_date_key",
"Date order Dispatched_Supplier",
"Date Order Delivered_Supplier";
SQL SELECT * from jnbvdw02a.edw.dbo.[qlk_fact_invoice_date];

"SupplyFeedback":
SELECT
*

FROM edw.dbo.
qlk_fact_sales_orders_supply_feedback;

left keep

"Forecast":
select
product_key,
period_key,
forecast_sales_value,
forecast_quantity
from jnbvdw02a.edw.dbo.vw_fact_jes_inventory;

left keep

"factDistributor":
select
product_key
,qty_available as StockAvailable
,qty_blocked_qc as StockBlocked
,reason_code
,reason_code_desc
from JNBVDW02A.edw.dbo.qlk_fact_distributor_stock
where
reason_code in (80,81,85);

left keep
"InvoicedOrders":
select
product_key,
snap_shot_period_key,
[Order_Qty] as [Invoice Order Qty]
FROM JNBVDW02A.edw.dbo.vw_fact_sales_invoiced_order;

Is this correct as I am not getting my result.

Eg,

I have product (A,B,C)

in SupplyFeedback table.

But I have product (A,B,C,D) in table Forecast

and product(B,C,D,E) in factDistributor.

I require :

The qlikview should only load product (A,B,C)

as output.

[Gysbert_Wassenaar]

In your script you have other load statements between SupplierOrders and factDistributor. Since these load statements don't immediately follow each other you need to specify which table should be used as reference table for the keep operation.

left keep ("SupplierOrders")

"factDistributor":

select

product_key

,qty_available as StockAvailable

,qty_blocked_qc as StockBlocked

,reason_code

,reason_code_desc

from JNBVDW02A.edw.dbo.qlk_fact_distributor_stock

where

reason_code in (80,81,85);