Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jblomqvist
Contributor II

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

Hello all,

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

ProductTable:


ProductIDName
10Shirt
31Shoes
44Wallet
55Jeans

OrdersTable:

   

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

1 Solution

Accepted Solutions
MVP
MVP

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

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)

1 Reply
MVP
MVP

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

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)