Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Combination of multiple fields - show in a listbox

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_nameinterest_1interest_2interest_3
aaMusicBooksPhotography
bbIndoor SportGardeningFashion
ccGadgetsTennis ClubChatting
ddMusicTravelPhotography

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

8 Replies
Sokkorn
Honored Contributor

Re: Combination of multiple fields - show in a listbox

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

Not applicable

Re: Combination of multiple fields - show in a listbox

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
Honored Contributor III

Re: Combination of multiple fields - show in a listbox

Even you can use expression in your ListBox.

Highlighted
Sokkorn
Honored Contributor

Re: Combination of multiple fields - show in a 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;

sujeetsingh
Honored Contributor III

Re: Combination of multiple fields - show in a listbox

Choo,

please refer this sample for your need.

Sokkorn
Honored Contributor

Re: Re: Combination of multiple fields - show in a listbox

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

Not applicable

Re: Re: Combination of multiple fields - show in a listbox

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

Not applicable

Re: Re: Combination of multiple fields - show in a listbox

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