Combination of multiple fields - show in a listbox

[#1]

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

[Sokkorn]

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

[#3]

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

[sujeetsingh]

Even you can use expression in your ListBox.

[Sokkorn]

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;

[sujeetsingh]

Choo,

please refer this sample for your need.

[Sokkorn]

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

[#8]

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

[#9]

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