Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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!
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
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
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.
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.
I just tried it out and maybe I modified it incorrectly, but I got a syntax error after the for loop:
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.
This didn't work quite well, but I tweaked it and I got exactly what I needed.
Thanks.