Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This should be simple and I've searched but not found or maybe not understood the answer
I have a pivot table showing Sku's (dimension) Host (Column) and Sales & Store Stock (Measures)
Lets say I have 100 units on hand in 'Host A' across 5 stores (20 units each)
and 2 stores have sales this week but the other 3 had none
The pivot will show correct Sales for the week but the stock figure will now only show 40 stock for the 2 stores that sold that week.
I want the pivot to show the total onhand stock of 100 for that HostA but because my store stock table is linked to the sales table the non sellers get filtered out.
How do I add the excluded stock to the result?
Hi Sunny,
I think I need a link table in my Data model between the sales and stock tables.
Having trouble creating the link table however as very new to the scripting so I'm trying random combinations (don't judge me)
I have an ODBC link to tables in an Access DB
I've loaded my sales (Sales:) and stock (StoreStock:) tables and added keys on
Host, Store, ChannelType, Sku AS key
I now want to create my linkTable between these but can't get the syntax
I'm assuming I have to pull the distinct values from the ODBC connection or is there a way to use the already loaded Sales: and StoreStock: tables?
Here' the link table load code at the moment
LIB CONNECT TO 'MS ACCESS';
LinkTable:
Load Distinct
Host & '|' & Store & '|' & ChannelType & '|' & Sku as Key,
Host,
Store,
ChannelType,
Sku;
SQL SELECT Key
FROM "X:\merchandising\retail tools\access database\Qlik Data.accdb".QlikSkuSalesByChannel;
*I get ERROR Field 'Host' not found on this 1st one
LinkTable:
Load Distinct
StkHost & '|' & StkSTore & '|' & StkChannelType & '|' & StkSku as Key,
StkHost,
StkSTore,
StkChannelType,
StkSku;
SQL SELECT StkHost & '|' & StkSTore & '|' & StkChannelType & '|' & StkSku as Key
FROM "X:\merchandising\retail tools\access database\Qlik Data.accdb".QlikStock;
Difficult to know how you have your data just by reading your explanation. Would it be possible to share a sample to see the issue?
Hi Sunny,
I think I need a link table in my Data model between the sales and stock tables.
Having trouble creating the link table however as very new to the scripting so I'm trying random combinations (don't judge me)
I have an ODBC link to tables in an Access DB
I've loaded my sales (Sales:) and stock (StoreStock:) tables and added keys on
Host, Store, ChannelType, Sku AS key
I now want to create my linkTable between these but can't get the syntax
I'm assuming I have to pull the distinct values from the ODBC connection or is there a way to use the already loaded Sales: and StoreStock: tables?
Here' the link table load code at the moment
LIB CONNECT TO 'MS ACCESS';
LinkTable:
Load Distinct
Host & '|' & Store & '|' & ChannelType & '|' & Sku as Key,
Host,
Store,
ChannelType,
Sku;
SQL SELECT Key
FROM "X:\merchandising\retail tools\access database\Qlik Data.accdb".QlikSkuSalesByChannel;
*I get ERROR Field 'Host' not found on this 1st one
LinkTable:
Load Distinct
StkHost & '|' & StkSTore & '|' & StkChannelType & '|' & StkSku as Key,
StkHost,
StkSTore,
StkChannelType,
StkSku;
SQL SELECT StkHost & '|' & StkSTore & '|' & StkChannelType & '|' & StkSku as Key
FROM "X:\merchandising\retail tools\access database\Qlik Data.accdb".QlikStock;