Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a situation where there are three tables:
1) Sales (Items Sold)
2) Orders (Items Ordered From Suppliers)
3) Returns (Sold items returned)
I need to bring the above three data sets together. But the issue is that specific keys are missing form the tables.
1) Sales table has : Prod_Code & Sales_Trans_ID
The product code is unique and one product code can have multiple Sales_Trans_ID
2) Order table has: Prod_Code
3) Returns table has: Sales_Trans_ID
What i need to do in a pivot table or a straight table is : Sales + Order - Returns = Balance
But don't know how to do it due to the missing keys. Below is an example of the issue I face because of the missing keys.
Prod_Code | Sales_Trans_ID | DATE | Trans_Type | Qty |
11111-1111-11 | 111 | 01-06-17 | DISPENSED QTY | 100 |
11111-1111-11 | 444 | 03-06-17 | DISPENSED QTY | 400 |
11111-1111-11 | KEY MISSING IN ORDERS | 04-06-17 | ORDERED QTY | 90 |
KEY MISSING IN RETURNS | 111 | 11-06-17 | RETURN QTY | 100 |
22222-2222-22 | 222 | 01-06-17 | DISPENSED QTY | 200 |
22222-2222-22 | KEY MISSING IN ORDERS | 01-06-17 | 200 | |
33333-3333-33 | 333 | 03-06-17 | DISPENSED QTY | 300 |
KEY MISSING IN RETURNS | 333 | 18-06-17 | RETURN QTY | 300 |
55555-5555-55 | 555 | 03-06-17 | DISPENSED QTY | 500 |
KEY MISSING IN RETURNS | 555 | 23-06-17 | RETURN QTY | 500 |
The Sales Table:
Date | Prod_Code | Sales_Trans_ID | Qty |
01-06-17 | 11111-1111-11 | 111 | 100 |
01-06-17 | 22222-2222-22 | 222 | 200 |
03-06-17 | 33333-3333-33 | 333 | 300 |
03-06-17 | 11111-1111-11 | 444 | 400 |
03-06-17 | 55555-5555-55 | 555 | 500 |
The Orders Table:
Date | Prod_Code | Qty |
01-06-17 | 22222-2222-22 | 200 |
04-06-17 | 11111-1111-11 | 90 |
The Returns Table:
Date | Sales_Trans_ID | Qty |
11-06-17 | 111 | 100 |
18-06-17 | 333 | 300 |
23-06-17 | 555 | 500 |
Please guide.
Note: Am working on QlikView 11.20 personal edition thus please put the load script in the reply will not be able to open QVW's.
Thanks.
Hi Cool W,
Try this:
Sales:
LOAD * INLINE [
Date, Prod_Code, Sales_Trans_ID, Qty
01/06/2017, 11111-1111-11, 111, 100
01/06/2017, 22222-2222-22, 222, 200
03/06/2017, 33333-3333-33, 333, 300
03/06/2017, 11111-1111-11, 444, 400
03/06/2017, 55555-5555-55, 555, 500
];
Orders:
LOAD * INLINE [
Date, Prod_Code, Qty
01/06/2017, 22222-2222-22, 200
04/06/2017, 11111-1111-11, 90
];
Return_NoProdKey:
LOAD * INLINE [
Date, Sales_Trans_ID, Qty
11/06/2017, 111, 100
18/06/2017, 333, 300
23/06/2017, 555, 500
];
MappingTransactionToProduct:
Mapping LOAD
Distinct
Sales_Trans_ID,
Prod_Code
Resident Sales;
NoConcatenate // to prevent auto-concatenation with Orders table
Returns:
Load
Date,
Sales_Trans_ID,
Qty,
ApplyMap('MappingTransactionToProduct',Sales_Trans_ID,'Unknown') as Prod_Code
Resident Return_NoProdKey;
DROP Table Return_NoProdKey;
NoConcatenate
[All Transactions]:
LOAD
'Sold' as TransType,
Date, Prod_Code, Sales_Trans_ID, -Qty as Qty //-Qty to indicate reduction in stock
Resident Sales;
Concatenate( [All Transactions])
LOAD
'Ordered' as TransType,
*
Resident Orders;
LOAD //Don't need Concatenate statement as this statenment loads table with identical fields to [All Transactions] will auto-concatenate
'Returned' as TransType,
*
Resident Returns;
Drop tables Sales,Orders,Returns;
this gives you this table:
Date | Prod_Code | Qty | Sales_Trans_ID | TransType |
---|---|---|---|---|
01/06/2017 | 11111-1111-11 | -100 | 111 | Sold |
01/06/2017 | 22222-2222-22 | -200 | 222 | Sold |
01/06/2017 | 22222-2222-22 | 200 | Ordered | |
03/06/2017 | 11111-1111-11 | -400 | 444 | Sold |
03/06/2017 | 33333-3333-33 | -300 | 333 | Sold |
03/06/2017 | 55555-5555-55 | -500 | 555 | Sold |
04/06/2017 | 11111-1111-11 | 90 | Ordered | |
11/06/2017 | 11111-1111-11 | 100 | 111 | Returned |
18/06/2017 | 33333-3333-33 | 300 | 333 | Returned |
23/06/2017 | 55555-5555-55 | 500 | 555 | Returned |
This straight table summarises (uncheck suppress zero values in the presentation tab)
Prod_Code | sum(Qty) |
---|---|
11111-1111-11 | -310 |
22222-2222-22 | 0 |
33333-3333-33 | 0 |
55555-5555-55 | 0 |
Good luck
Andrew
Hi Cool W,
Try this:
Sales:
LOAD * INLINE [
Date, Prod_Code, Sales_Trans_ID, Qty
01/06/2017, 11111-1111-11, 111, 100
01/06/2017, 22222-2222-22, 222, 200
03/06/2017, 33333-3333-33, 333, 300
03/06/2017, 11111-1111-11, 444, 400
03/06/2017, 55555-5555-55, 555, 500
];
Orders:
LOAD * INLINE [
Date, Prod_Code, Qty
01/06/2017, 22222-2222-22, 200
04/06/2017, 11111-1111-11, 90
];
Return_NoProdKey:
LOAD * INLINE [
Date, Sales_Trans_ID, Qty
11/06/2017, 111, 100
18/06/2017, 333, 300
23/06/2017, 555, 500
];
MappingTransactionToProduct:
Mapping LOAD
Distinct
Sales_Trans_ID,
Prod_Code
Resident Sales;
NoConcatenate // to prevent auto-concatenation with Orders table
Returns:
Load
Date,
Sales_Trans_ID,
Qty,
ApplyMap('MappingTransactionToProduct',Sales_Trans_ID,'Unknown') as Prod_Code
Resident Return_NoProdKey;
DROP Table Return_NoProdKey;
NoConcatenate
[All Transactions]:
LOAD
'Sold' as TransType,
Date, Prod_Code, Sales_Trans_ID, -Qty as Qty //-Qty to indicate reduction in stock
Resident Sales;
Concatenate( [All Transactions])
LOAD
'Ordered' as TransType,
*
Resident Orders;
LOAD //Don't need Concatenate statement as this statenment loads table with identical fields to [All Transactions] will auto-concatenate
'Returned' as TransType,
*
Resident Returns;
Drop tables Sales,Orders,Returns;
this gives you this table:
Date | Prod_Code | Qty | Sales_Trans_ID | TransType |
---|---|---|---|---|
01/06/2017 | 11111-1111-11 | -100 | 111 | Sold |
01/06/2017 | 22222-2222-22 | -200 | 222 | Sold |
01/06/2017 | 22222-2222-22 | 200 | Ordered | |
03/06/2017 | 11111-1111-11 | -400 | 444 | Sold |
03/06/2017 | 33333-3333-33 | -300 | 333 | Sold |
03/06/2017 | 55555-5555-55 | -500 | 555 | Sold |
04/06/2017 | 11111-1111-11 | 90 | Ordered | |
11/06/2017 | 11111-1111-11 | 100 | 111 | Returned |
18/06/2017 | 33333-3333-33 | 300 | 333 | Returned |
23/06/2017 | 55555-5555-55 | 500 | 555 | Returned |
This straight table summarises (uncheck suppress zero values in the presentation tab)
Prod_Code | sum(Qty) |
---|---|
11111-1111-11 | -310 |
22222-2222-22 | 0 |
33333-3333-33 | 0 |
55555-5555-55 | 0 |
Good luck
Andrew
Hi,
If I have understood like this ?
Hi Andrew,
Works, thanks a ton.
Just one query. I made a Pivot Table and wanted to get the Total Amount by Prod_Code.
Selected Show Partial Sums as below but am getting the total figure only for one of the three Prod_Code.
Please advise.
Thanks again for taking the time out to help
Would be nice to know how you got the above Pivot. Mean the Expressions.
Thanks.
Hi Cool W,
In the presentation tab de-select Supress Zero Values:
Prod_Code | Sales_Trans_ID | Date | TransType | Sum(Qty) |
---|---|---|---|---|
11111-1111-11 | 111 | 01/06/2017 | Sold | -100 |
11/06/2017 | Returned | 100 | ||
444 | 03/06/2017 | Sold | -400 | |
- | 04/06/2017 | Ordered | 90 | |
Total | -310 | |||
22222-2222-22 | 222 | 01/06/2017 | Sold | -200 |
- | 01/06/2017 | Ordered | 200 | |
Total | 0 | |||
33333-3333-33 | 333 | 03/06/2017 | Sold | -300 |
18/06/2017 | Returned | 300 | ||
Total | 0 | |||
55555-5555-55 | 555 | 03/06/2017 | Sold | -500 |
23/06/2017 | Returned | 500 | ||
Total | 0 |
LOAD Date,
Prod_Code,
Sales_Trans_ID,
Qty,'S' as Type
FROM
"https://community.qlik.com/message/1298663"
(html, codepage is 1252, embedded labels, table is @2);
Join
LOAD Date,
Prod_Code,
Qty as QtyO,'O' as Type
FROM
"https://community.qlik.com/message/1298663"
(html, codepage is 1252, embedded labels, table is @3);
Join
LOAD //Date,
Sales_Trans_ID,
Qty as QtyR
FROM
"https://community.qlik.com/message/1298663"
(html, codepage is 1252, embedded labels, table is @4);
Expressions :
Sum(Qty)+Sum(QtyO)
Sum(QtyR)
Sum(Qty)+Sum(QtyO)-Sum(QtyR)
Antonio, thanks a ton.
Appreciate all your help.