Is there a way to extract out all possible values from a column and display them in a table chart?

[QlikToFindOut]

Hello,

I currently have a table where there is one column in particular where I want to extract values from. Those values from the column are like such:

ID	KITCHEN ITEM
1	FORK, CANDLE
2	SPOON, SPORK
3	KNIFE, CANDLE
4	FORK, CANDLE
5	KNIFE, SPOON, FORK, CANDLE
6	SPOON, FORK, CANDLE
7	FORK

I am trying to extract that data from the table and display it in a table chart in the Sheet like such:

Kitchen Item Extracted	Count of Items
KNIFE	2
SPOON	3
SPORK	1
FORK	5
CANDLE	5

My original thought was to use this function and do it in the script:

TEXT(TRIM(SUBFIELD([Kitchen Items], ','))) AS [Kitchen Items Extracted]

However, the problem with this method is it made my app worse in three ways:

1. It exponentially increased the number of rows in my table
2. It is incredibly inefficient
3. Getting a summation or total count for the original number of rows in the table is impossible (for example, the table originally had 1000 rows and with the code above, there is now 100,000 rows)

Is there a way to accomplish this in the sheet level? Or is there a more efficient way to do this in the script?

Thanks!

[Vegar · Accepted solution]

Let's keep your 1000 rows table as is, but instead create a dimension table for kitchen items.

Transactions:
LOAD
ID,
[KITCHEN ITEM] as %kitchenItemID,
Dim1,
Dim2,
DimN
FROM Source;

//create a dimension table
for each _id in
fieldvaluelist('%kitchenItemID')
[Kitchen items]:
Load
'$(_id)' as %kitchenItemID,
TEXT(TRIM(SUBFIELD('$(_id)', ','))) AS [Kitchen Item]
Autogenerate 1;
next _id

[QlikToFindOut]

Thanks!

If I make a selection in the source table, would the selection also reflect in the newly created table?

If I select “FORK”, would it automatically filter the new table on FORK?

Thanks. 

[Vegar]

If you select FORK in your new table then the ID 1, 4, 5, 6 and 7 will be associated to that selection.

If you select ID 2 then the kitchen items SPORK and SPOON will be associated. 

I suggest you try it out on the small dataset that you provided to us when creating this post to see how my solution will behave.

[QlikToFindOut]

I just tried it out and maybe I modified it incorrectly, but I got a syntax error after the for loop:

Transactions:

LOAD ID

,[KITCHEN ITEM] AS %kitchenDocumentID

RESIDENT Inventory

;

for each _id in 

fieldvaluelist('%kitchenDocumentID')

[Kitchen items]:

Load 

'$(_id)' as %kitchenDocumentID,

TEXT(TRIM(SUBFIELD('$(_id)', ','))) AS [Kitchen Document Item]

Autogenerate 1;

next _id

I get a syntax error on [Kitchen items]. Any thoughts why?

Thanks!

[Vegar]

I don't see why, except that you might have a line break between 'in' and 'fieldvaluelist'. You can not have a line break there.

Since the for each..next statement is a control statement and as such is ended with either a semicolon or end-of-line, each of its three possible clauses (for each, exit for and next) must not cross a line boundary.

If that is not the issue then please take a screenshot of your error message and post it here.

[QlikToFindOut]

This didn't work quite well, but I tweaked it and I got exactly what I needed.

Thanks.