Analyze the combination of values

[#1]

Dear Qlikview-Community,

I'm struggeling with the problem following and would appreciate your help:

I've got one Table:

SALES

----------

Sales Order Number

Product

Product Variant

The Field Variant has is a string of many comma-separated values.

EXAMPLE:

Sales Order Number: 101010000

Product: PPP2

Product Variant: X1,Y5,K4

How could I make a table of all these combinations?

Creating a table with hundreds of columns does not make sense. Does it?

Every column should have a function like [Code] substringcount([Product Variant]),'X1') [/Code]

How could I solve this?

Thank you very much for your help.

[#2]

Hi,

try to use Subfield() function. For example:

LOAD
[Sales Order Number],
Product,
[Product Variant],
Subfield([Product Variant], ',') AS Variant
FROM ....

So instead of this table:

Order	Product	Product variant
101010000	PPP1	X1,Y5,K4

u will get something like this:

Order	Product	Variant
101010000	PPP1	X1
101010000	PPP1	Y5
101010000	PPP1	K4

Milda

[#3]

Dear Milda,

thank you very much for your help.

I already have done this. Exactly how you recommended.

The question is, to count how many Variants a Product has in all Orders.

Meaning: I need to know how many times appears a combination of X1 and Y5 for instance.

If I would do this in a pivot table by making hundreds of columns.

[johnw]

So for this data:

Order Product Variant
1 A X1, Y5, K4
2 A X2
3 A Y4, Y5
4 A Y5, K4, K5
5 B X1, X2, Y4, Z7, Z2

You want to, say, select product A, and see a table like this?

X1 X2 Y4 Y5 K4 K5
X1 1 0 0 1 1 0
X2 0 1 0 0 0 0
Y4 0 0 1 1 0 0
Y5 1 0 1 3 2 1
K4 1 0 0 2 2 1
K5 0 0 0 1 1 1

I'm just confirming the requirement. If that's what you want, I've poked at it before, but wasn't able to solve it.

[#5]

Yes John, that's exactly what I'm searching for.

[johnw]

OK, I'm not sure how well this will scale up to a full size application, but one way is to create a second table with Order and a duplicate Variant field. Create a pivot table with the two variants as dimensions, and count(distinct Order). See attached.