How To Find Different Combinations From a Table

[#1]

Dear All.

I have a table like this:

Item 1	Item 2	Item 3	Item 4
1234	Yellow	4	FS1
455678	Green	5	FS2
1234	Red	5	FS2
6789	Blue	5	FS1
455678	Green	4	FS2
6789	Red	5	FS1
25676	Blue	5	FS2
1234	Yellow	5	FS1

Each row of this table is a KIT of elements, for example the KIT Number 1 will be the elements 1234, Yellow, 4 and FS1, that belongs to all elements in the Number 1 Row.

My idea is try to find how many Diferent Kits i have and if is possible add a New Column with the Kits Name for example KIT1, KIT2.... ETC.

Sorry for my english.

I really appreciate your Help.

BR

[Gysbert_Wassenaar · Accepted solution]

You can use the autonumber function to create the unique KIT ID's:

MyTable:

Load Item1, Item2, Item3, Item4, 'KIT' & autonumber( Item1 & Item2 & Item3 & Item4) as KitID

From ...

[MK_QSL]

May be something like this....

Temp:

Load * Inline

[

  Item 1, Item 2, Item 3, Item 4

  1234, Yellow, 4, FS1

  455678, Green, 5, FS2

  1234, Red, 5, FS2

  6789, Blue, 5, FS1

  455678, Green, 4, FS2

  6789, Red, 5, FS1

  25676, Blue, 5, FS2

  1234, Yellow, 5, FS1

];

NoConcatenate

Final:

Load Distinct [Item 1] Resident Temp;

Join

Load Distinct [Item 2] Resident Temp;

Join

Load Distinct [Item 3] Resident Temp;

Join

Load Distinct [Item 4] Resident Temp;

Drop Table Temp;

[#4]

Is not possible do it like this, because i have a table with more or less 10000 Rows, in my question i only show 8 rows as example, sorry i dont clarify this.

But finally the idea is that Qlikview detect automatically how many combinations i have and assign a name to each combination.
BR

[#5]

Your answer was so helpfully, i used it and it works.

Thanks a Lot.

[rwunderlich]

Assumming that the table is already DISTINCT, you can create the Kit ids when you load the table by adding a field

,'KIT' & RownNo() as KitId

If the table is already loaded, you can add the Ids with a JOIN:

LEFT JOIN (Kits)

LOAD DISTINCT *, 'KIT' & RowNo() as Kit RESIDENT Kits;

And if the table is not DISTINCT, you can do a two step process to generate DISTINCT ids and join them to the table:

KitIds:

LOAD DISTINCT *, 'KIT' & RowNo() as Kit RESIDENT Kits;

LEFT JOIN (Kits) LOAD * RESIDENT KitIds;

Drop Table KitIds;

-Rob

http://masterssummit.com

http://robwunderlich.com