how to merge the tables having different granularity

[taha_mansoor]

Hi Everyone, I want to know that if there is a way to merge my Shipments table with Orders and Invoices table. The Invoices and Orders table has same granularity (same number of rows) but shipments table is having more rows means multiple shipments goes to 1 order.  Below is an Inline script and model view picture showing up the case.

Invoices:
LOAD
OrderID&'-'&InvoiceLine as Order_Invoice_KEY,
InvoiceID,
CustID,
InvoiceLine,
SaleAmount
;
LOAD * INLINE [
InvoiceID, CustID, InvoiceLine, SaleAmount, OrderID
100, A, 1, 5, 1000
100, A, 2, 5, 2000
200, B, 1, 10, 3000
200, B, 2, 5, 4000
200, B, 3, 5, 5000
300, C, 1, 10, 6000
300, C, 2, 5, 7000
];

Orders:
LOAD
OrderID,
OrderID&'-'&InvoiceLine as Order_Invoice_KEY
;
LOAD * INLINE [
OrderID, InvoiceID, InvoiceLine
1000, 100, 1
2000, 100, 2
3000, 200, 1
4000, 200, 2
5000, 200, 3
6000, 300, 1
7000, 300, 2
];

Shipment:
LOAD * INLINE [
ShipID, OrderID
1001, 1000
1002, 1000
1003, 1000
2001, 2000
2002, 2000
2003, 2000
2004, 2000
2005, 2000
3001, 3000
3002, 3000
3003, 3000
4001, 4000
4002, 4000
4003, 4000
5001, 5000
5002, 5000
6001, 6000
6002, 6000
7001, 7000
7002, 7000
];

My aim is to merge the Shipments table with Orders an Invoices table.

Is there any way to merge these data sets in order to get star schema design?

Qlik Sense Enterprise on Windows

[abhijitnalekar]

Hi @taha_mansoor ,

Please try below below script

Shipment:
mapping
LOAD * INLINE [
ShipID, OrderID
1001, 1000
1002, 1000
1003, 1000
2001, 2000
2002, 2000
2003, 2000
2004, 2000
2005, 2000
3001, 3000
3002, 3000
3003, 3000
4001, 4000
4002, 4000
4003, 4000
5001, 5000
5002, 5000
6001, 6000
6002, 6000
7001, 7000
7002, 7000
];

Orders:
LOAD
OrderID,
OrderID&'-'&InvoiceLine as Order_Invoice_KEY,
applymap('Shipment',OrderID,'NO') as ShipID
;
LOAD * INLINE [
OrderID, InvoiceID, InvoiceLine
1000, 100, 1
2000, 100, 2
3000, 200, 1
4000, 200, 2
5000, 200, 3
6000, 300, 1
7000, 300, 2
];

left join (Orders)
Invoices:
LOAD
OrderID&'-'&InvoiceLine as Order_Invoice_KEY,
InvoiceID,
CustID,
InvoiceLine,
SaleAmount
;
LOAD * INLINE [
InvoiceID, CustID, InvoiceLine, SaleAmount, OrderID
100, A, 1, 5, 1000
100, A, 2, 5, 2000
200, B, 1, 10, 3000
200, B, 2, 5, 4000
200, B, 3, 5, 5000
300, C, 1, 10, 6000
300, C, 2, 5, 7000
];

[taha_mansoor]

@abhijitnalekar 

1 order has multiple shipments therefore applymap is not working on this.

Is there any other practice to handle this situation?

[vikasmahajan]

Hi,

Have you try this link table concept 

https://community.qlik.com/t5/Qlik-Sense-Documents/link-table/ta-p/1883375

vikas