Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community,
I have a table in this format where the interests are separated in 3 columns, I want to show all in one listbox, so i have a script as shown below, then i can view all in a listbox.
However, when i select any one of interests, it doesn't affect to the chart. Any one knows how to combine 3 fields into one fields?
f_name | interest_1 | interest_2 | interest_3 |
aa | Music | Books | Photography |
bb | Indoor Sport | Gardening | Fashion |
cc | Gadgets | Tennis Club | Chatting |
dd | Music | Travel | Photography |
Script |
---|
Piper: LOAD name, interest_1, interest_2, interest_3 FROM (ooxml, embedded labels, table is Sheet1); Interest1: Load interest_1 as AllInterest Resident Piper; Interest2: Load interest_2 as AllInterest Resident Piper; Interest3: Load interest_3 as AllInterest Resident Piper; |
Thanks,
Chanel
Hi Ms. Chanel,
How do you think if we combine 3 field into one. Like this
Piper:
LOAD
name,
interest_1,
interest_2,
interest_3,
interest_1 &'-'&interest_2&'-'&interest_3 as AllInterest
FROM
(ooxml, embedded labels, table is Sheet1);
Regards,
Sokkorn
Dear Sokkorn,
Thanks for fast response!
However, I want to show the listbox in this way:
but not "Books - Chatting - Fashion", "Gadgets - Gardening - Music" ...
Thanks,
Chanel
Even you can use expression in your ListBox.
Hi Chanel,
Got it . Do this way.
Piper:
LOAD name,
interest_1,
interest_2,
interest_3
FROM
(ooxml, embedded labels, table is Sheet1);
Interest1:
Load interest_1 as AllInterest
Resident Piper;
Interest2:
Concatenate(Interest1)
Load interest_2 as AllInterest
Resident Piper;
Interest3:
Concatenate(Interest1)
Load interest_3 as AllInterest
Resident Piper;
Choo,
please refer this sample for your need.
Hi Chanel,
I think this data model more better
[Source]:
LOAD * Inline [
f_name, interest_1, interest_2, interest_3
aa, Music, Books, Photography
bb, Indoor Sport, Gardening, Fashion
cc, Gadgets, Tennis Club, Chatting
dd, Music, Travel, Photography];
[Interest1]:
LOAD
f_name,
interest_1 AS Interest,
'Interest1' AS InterestType
Resident [Source];
[Interest2]:
LOAD
f_name,
interest_2 AS Interest,
'Interest2' AS InterestType
Resident [Source];
[Interest3]:
LOAD
f_name,
interest_3 AS Interest,
'Interest3' AS InterestType
Resident [Source];
DROP Table [Source];
So after reload script, we can
1. Create ListBox using field Interest
2. Create Straight table with
a. Dimension: f_name
b. Expression:
Interest 1 = Only({$<InterestType={'Interest1'}>} Interest)
Interest 2 = Only({$<InterestType={'Interest2'}>} Interest)
Interest 3 = Only({$<InterestType={'Interest3'}>} Interest)
See sample attached file
Regards,
Sokkorn
Dear Sokkorn,
Really thanks for your effort to create the example.
But in this case, we will have 3 sets same/duplicate data for different interest.
My document has huge data so this way might not suitable.
Best regards,
Chanel
Thanks for your sample. But i failed to reload with that script, this might because my data set is huge. The join statement caused it to collapse...
Thanks,
Chanel