topic 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

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

swuehl — Fri, 03 Jun 2016 10:28:31 GMT

Maybe like this:

SET DateFormat = 'DD/MM/YYYY';

[Schedule_Outlets]:

LOAD Outlet_Code as Scheduled_Outlet_Code, Sales_Man_Code, CategoryID

INLINE [

Outlet_Code, Sales_Man_Code, CategoryID

T00019003004005, 1003, A001

T00019003004003, 1003, A001

T00019003004006, 1003, A001

T00019003004009, 1003, A001

];

Product:

LOAD * INLINE [

CategoryID, ProductCode, ProductName

A001, 30008, Chocolate 200g

A001, 30009, Chocolate 300g

A001, 30010,Milk Chocolate 200g

];

Order:

LOAD * INLINE [

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

];

Then create a straight table chart with ProductCode as dimension and four expressions:

=Date(Min(Order_Date))

=Date(Max(Order_Date))

=Count(DISTINCT Scheduled_Outlet_Code)

=Count(DISTINCT Outlet_Code)

ProductCode	From	To	Count(DISTINCT Scheduled_Outlet_Code)	Count(DISTINCT Outlet_Code)
30008	06/01/2016	06/01/2016	4	1
30009	05/01/2016	06/01/2016	4	2
30010	06/01/2016	06/01/2016	4	1
300010	05/01/2016	05/01/2016	0	1

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

Kushal_Chawda — Fri, 03 Jun 2016 10:34:57 GMT

Rename Outlet_code from any of the Table, then load all the table, now there will not be any loops

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

Anonymous — Sat, 04 Jun 2016 06:48:23 GMT

Thanks for your reply...

Now if I want to add "Outlet Master" table with this data model and need to join 'Outlet_Code' with both Scheduled & Order table then how it is possible.

Outlet_Master:

LOAD * INLINE [

Outlet_Code, Outlet_Name, Outlet_Type

T00019003004005, ABC Store, Retailer

T00019003004003, AA Super Store, Super Store

T00019003004006, BB General Store, General Store

T00019003004009, AB General Store, General Store

];

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

swuehl — Sat, 04 Jun 2016 09:37:53 GMT

Maybe have a look at

Why You sometimes should Load a Master Table several times

Circular References