Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
coolwaters
Contributor II
Contributor II

Tables with missing keys

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.

1 Solution

Accepted Solutions
effinty2112
Master
Master

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/201711111-1111-11-100111Sold
01/06/201722222-2222-22-200222Sold
01/06/201722222-2222-22200Ordered
03/06/201711111-1111-11-400444Sold
03/06/201733333-3333-33-300333Sold
03/06/201755555-5555-55-500555Sold
04/06/201711111-1111-1190Ordered
11/06/201711111-1111-11100111Returned
18/06/201733333-3333-33300333Returned
23/06/201755555-5555-55500555Returned

This straight table summarises (uncheck suppress zero values in the presentation tab)

Prod_Code sum(Qty)
11111-1111-11-310
22222-2222-220
33333-3333-330
55555-5555-550

Good luck

Andrew

View solution in original post

7 Replies
effinty2112
Master
Master

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/201711111-1111-11-100111Sold
01/06/201722222-2222-22-200222Sold
01/06/201722222-2222-22200Ordered
03/06/201711111-1111-11-400444Sold
03/06/201733333-3333-33-300333Sold
03/06/201755555-5555-55-500555Sold
04/06/201711111-1111-1190Ordered
11/06/201711111-1111-11100111Returned
18/06/201733333-3333-33300333Returned
23/06/201755555-5555-55500555Returned

This straight table summarises (uncheck suppress zero values in the presentation tab)

Prod_Code sum(Qty)
11111-1111-11-310
22222-2222-220
33333-3333-330
55555-5555-550

Good luck

Andrew

antoniotiman
Master III
Master III

Hi,

If I have understood like this ?

coolwaters
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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

Thanks.

effinty2112
Master
Master

Hi Cool W,

In the presentation tab de-select Supress Zero Values:

Prod_Code Sales_Trans_ID Date TransType Sum(Qty)
11111-1111-1111101/06/2017Sold-100
11/06/2017Returned100
44403/06/2017Sold-400
-04/06/2017Ordered90
Total -310
22222-2222-2222201/06/2017Sold-200
-01/06/2017Ordered200
Total 0
33333-3333-3333303/06/2017Sold-300
18/06/2017Returned300
Total 0
55555-5555-5555503/06/2017Sold-500
23/06/2017Returned500
Total 0
antoniotiman
Master III
Master III

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
Contributor II
Contributor II
Author

Antonio, thanks a ton.

Appreciate all your help.