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

jblomqvist — Fri, 03 Nov 2017 17:48:40 GMT

Hello all,

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

ProductTable:

OrdersTable:

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!)

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

sunny_talwar — Fri, 03 Nov 2017 18:05:55 GMT

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)