listbox with categorys to filter table with csv-like content

[#1]

Hey,

I need to get some tricky task done but I'm stuck. I have a table with following content. The drink collumn contains strings in a csv-like way.

id	name	lastname	drink
1	brown	peter	beer;vodka;wine
2	green	jack	wine;beer
3	blue	paul	beer
4	white	jeff	vodka

I also have a table with the "drink categorys":

drinks
beer
wine
vodka

I load both tables into my QlikView document. Now it automatically links "drinks" so if I create a listbox with the field drinks it shows the table content of the first table with "beer;vodka;wine" and so on. BUT I would like to get a listbox with the categorys so that I can filter the original content-table with the categorys and a function like "is content of" or sth. like that ... so how can I realize this? Glad about any help!

Thanks for replys!

BR

Henrik

[#2]

Hi enricoba,

have a look at the subfield function, if you load your 'drink' field as

Subfield(drink,';') AS drink

this will split the field up into the respective contents and create you a row for each, you can then join your category as you intend to the individual values

hope that helps

Joe

[Anonymous]

See attached

[#4]

hey Joe,

thanks for your reply. This works in first place, but it kind of destroys the structure of my original table. And I would like to keep the structure in QlikView. That I have a single row for every entry. Any other solution to fix that?

Thanks

BR

Henrik

[#5]

Thanks for reply michael,

but I only got a personal version of QlikView so I can't open your document without losing the connection to my files. If I am wrong please correct me .

BR

Henrik

[Anonymous · Accepted solution]

My example is using the same subfield as Joe suggested.  If you want to keep intact the original table, create additional table Link, see the script:

Data:
LOAD * INLINE [
id, name, lastname, drink
1, brown, peter, beer;vodka;wine
2, green, jack, wine;beer
3, blue, paul, beer
4, white, jeff, vodka];

categories:
LOAD * INLINE [
drinks
beer
wine
vodka
];

Link:
LOAD
drink,
subfield(drink,';') as drinks
RESIDENT Data;

[#7]

if you need to keep the table structure as is, use the subfield within a link table as Michael suggests below

[#8]

@msolomov,

after some trys I finally got it. Thanks a lot! I really appreciate this supportive Commnity!

BR

Henrik

[#9]

I know I should open a new question, but this is a short one and directly connected to the main thread topic.

How do I handle exactly the same situation if I have more then one column with categories?

Just imagine my table has one more coulumn with let's say "fruit". So I load it as following:

Data:
LOAD * INLINE [
id, name, lastname, drink, fruit
1, brown, peter, beer;vodka;wine, orange;apple
2, green, jack, wine;beer, apple
3, blue, paul, beer, apple;orange;banana
4, white, jeff, vodka, banana];

categories:
LOAD * INLINE [
drinks, fruits
beer, apple
wine, banana
vodka, orange
];

1. Option:

I tried to do the same as you showed me, but it leads to a weird behaviour of my original table if I add more categories.

Link:
LOAD
drink,

fruit,
subfield(drink,';') as drinks,

subfield(fruit,';') as fruits
RESIDENT Data;

2. Option:

I try to create more "links" but that leads to another error.

Link:
LOAD
drink,
subfield(drink,';') as drinks
RESIDENT Data;

Link2:
LOAD
fruit,
subfield(fruit,';') as fruits
RESIDENT Data;

What would be the correct way to handle this? Thanks again ... !

BR

Henrik

[Anonymous]

Henrik,

Your option #2 is correct, but the categories table is not.  You probably don't want to associate beer only with apple and vodka only with orange.  Here what I expect:

categories_drink:
LOAD * INLINE [
drinks
beer
wine
vodka,
];

categories_fruit:
LOAD * INLINE [
  fruits
  apple
banana
  orange
];