Brain teaser: How to analyse multiple products ordered held in one field per order?

[jblomqvist]

Hello all,

I have some data I am working with that looks like this:

ProductTable:

ProductID	Name
10	Shirt
31	Shoes
44	Wallet
55	Jeans

OrdersTable:

OrderID	ProductID	Quanity
432313	[10]	3
56676	[31, 55]	34
232323	[]	66
5456767	[55, 44, 10, 31]	22

You can see the problem

Some orders can have one product

Some orders can have multiple products

Some orders can have no products (yes)

What I have to do is provide number of orders per product (e.g. something like a Bar Chart).

How can I provide this accurately as possible using Qlik?

(The data is not great I know!)

[sunny_talwar · Accepted solution]

I would create a new field using SubField function to make your life easier here

Table:

LOAD OrderID,

     ProductID,

     Quantity

FROM ...;

LinkTable:

LOAD ProductID,

     SubField(ProductID, ', ') as UniqueProductID

Resident Table;

One you do this, create a chart with

Dimension

UniqueProductID

Expression

Count(DISTINCT OrderID)