How to map child data to the main record in qlikview

The problem:
You have a main record, let's say a sales order and you want to know if a welcome pack has been included on this order. A welcome pack is a sales order line item just like any other product on the order. There is no way on the main order line to know if a welcome pack has been included with this order. The only way to find that out is to go through every sales order line item and find out if any of the line items are in fact a welcome pack.

The loaded data comes from three different files:

• MainOrderLines.csv
• OrderLineItems.csv
• Customers.csv

The model in Qlikview model looks like this:

The Qlikview model. You can see the salesOrderId is connecting the line items to the main order line and the customer id  connects the customer to the order. That is a customer can have many order and an order can have many line items.

The welcome packs are special line items on the orders and they can be identified by their name, it always starts with "Welcome Pack".

The Solution:
Build a map in Qlikview and load it as a column on the main line of the order record. This column can be called sales_order_welcome_pack for instance. It shall contain either the name of the Welcome pack e.g "Welcome Pack A". If there is no welcome pack it shall say "No Welcome Pack".

How to implement this:
Load the line item CSV file in to a Map filtering out only the welcome packs. Index the map on a unique order number.

It will look something like this:


OrderWelcomePackMap:
MAPPING LOAD  DISTINCT sales_order_internal_id, item_display_name
FROM
C:\Users\johne\results*.csv
(txt, utf8, embedded labels, delimiter is ',', msq) WHERE item_bkfno LIKE 'Welcome Pack *';

This way the map contains data like this:
id, name
123, Welcome Pack A
124, Welcome Pack B

We have identified all the welcome packs and identified the order main line that shall be populated with the data.

Now it is time to use the map to look up the data at load time of the main order data.

This piece of code in the load script will tag all the sales order main lines with the correct welcome pack. It will tag orders without welcome pack as "No Welcome Pack";

ApplyMap('OrderWelcomePackMap', sales_order_internal_id, 'No welcome Pack') as sales_order_welcome_pack


Now we can create a Welcome pack picker in the Qlikview UI and still have all the items on the orders with the specific welcome pack in the selection.

An example of what the welcome pack selector could look like.

This type of implementation pattern is very useful for aggregating data hidden on the line items up to the main record.

Another example could be to create a map with only orders with an item of a certain category. Only tag orders that have a line item above a certain value. I could go on but you get the message...

Please leave a comment if you know a better way of implementing this.