topic Re: How to manage total outlets vs ordered outlets in data model in QlikView

How to manage total outlets vs ordered outlets in data model

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

@I am trying to manage tables for total outlets vs ordered outlets in QVW but getting LOOPs.

Is anyone have solution for this issue. Please help to sort out it.

Table: Schedule_Outlets
Outlet_Code	Sales_Man_Code	CategoryID
T00019003004005	1003	A001
T00019003004003	1003	A001
T00019003004006	1003	A001
T00019003004009	1003	A001
Table: Product
CategoryID	ProductCode	ProductName
A001	30008	Chocolate 200g
A001	30009	Chocolate 300g
A001	30010	Milk Chocolate 200g
Table: Order
OrderID	Order_Date	ProductCode	Outlet_Code
AZ1610045769	05/01/2016	30009	T00019003004003
AZ1610045769	05/01/2016	300010	T00019003004003
AZ1610045770	06/01/2016	30008	T00019003004006
AZ1610045770	06/01/2016	30009	T00019003004006
AZ1610045770	06/01/2016	30010	T00019003004006
How to join these tables because I am getting "LOOP" when I join then in QVW
I need following results.
ProductCode	From_Date	To_Date	Total_Schedule_Outlets	Total_Productive_Outlets	%age
30009	05/01/2016	06/01/2016	4	2	50%

Re: How to manage total outlets vs ordered outlets in data model

Anonymous — Mon, 23 May 2016 09:29:42 GMT

Does anyone have solution for this issue, please help

Re: How to manage total outlets vs ordered outlets in data model

sunny_talwar — Mon, 23 May 2016 15:26:24 GMT

If this isn't resolved why have you masked it as assumed answer? Remove that so that people can consider helping you

Re: How to manage total outlets vs ordered outlets in data model

Anonymous — Mon, 23 May 2016 20:39:26 GMT

Now there is no option to remove assumed answer

Please help if you have solution.

Re: How to manage total outlets vs ordered outlets in data model

Anonymous — Sat, 28 May 2016 06:34:49 GMT

Please help if you have its solution

Re: How to manage total outlets vs ordered outlets in data model

sunny_talwar — Sun, 29 May 2016 03:59:43 GMT

Is this what you are trying to get?

Script:

Schedule_Outlets:

LOAD Outlet_Code,

Sales_Man_Code,

CategoryID as CatID

FROM

[Total Outlets Vs Ordered Outlets by Product.xlsx]

(ooxml, embedded labels, table is Schedule_Outlets);

Product:

LOAD CategoryID,

ProductCode,

ProductName

FROM

[Total Outlets Vs Ordered Outlets by Product.xlsx]

(ooxml, embedded labels, table is Product);

Order:

LOAD OrderID,

Order_Date,

ProductCode,

Outlet_Code

FROM

[Total Outlets Vs Ordered Outlets by Product.xlsx]

(ooxml, embedded labels, table is Order);

Data Model:

Re: How to manage total outlets vs ordered outlets in data model

Anonymous — Sun, 29 May 2016 08:06:55 GMT

Thanks for sparing your time, I appreciate.

Yes I need similar results but 'Total_Schedule_Outlets' should be count of all outlets those belong to relevant Product CategoryID.

By given solution QlikView shows Schedule Outlets with EQUAL join to Productive Outlets at my end. i.e. Schedule 2 & Productive 2.

How did you calculate 4 in Schedule Outlets ?

Re: How to manage total outlets vs ordered outlets in data model

sunny_talwar — Mon, 30 May 2016 10:17:41 GMT

I am not sure I follow you Imran. Would you be able to elaborate?