topic Re: How to restrict product from one table. in QlikView

How to restrict product from one table.

suvechha_b — Wed, 12 Aug 2015 11:21:23 GMT

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.

Re: How to restrict product from one table.

Gysbert_Wassenaar — Wed, 12 Aug 2015 11:31:41 GMT

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);

Re: How to restrict product from one table.

suvechha_b — Wed, 12 Aug 2015 13:27:56 GMT

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.

Re: How to restrict product from one table.

Gysbert_Wassenaar — Fri, 14 Aug 2015 07:35:15 GMT

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);