Tables with missing keys

[coolwaters]

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.

[effinty2112 · Accepted solution]

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

[antoniotiman]

Hi,

If I have understood like this ?

[coolwaters]

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

[coolwaters]

Would be nice to know how you got the above Pivot. Mean the Expressions.

Thanks.

[effinty2112]

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

[antoniotiman]

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)

[coolwaters]

Antonio, thanks a ton.

Appreciate all your help.