Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey Guys,
I have this table:
item_id | Food_name | Food_Price | Classification | Classifications |
1 | hamburguer | 10 | unhealthy | |
2 | ice cream | 11 | unhealthy | |
3 | steak | 12 | unhealthy | |
4 | bread | 13 | healthy | |
5 | coke | 14 | unhealthy | |
6 | juice | 1 | healthy | |
7 | green salad | 2 | healthy | |
8 | mango | 3 | healthy | |
9 | ham | 15 | healthy | |
10 | cesar salad | 16 | unhealthy | |
11 | chicken | 8 | healthy | |
12 | fish | 8 | healthy | |
13 | tofu | 5 | healthy | |
A | juice | 0.5 | healthy | coke |
B | green salad | 1.5 | healthy | cesar salad |
C | mango | 2.5 | healthy | ice cream |
D | chicken | 7 | healthy | hamburguer |
E | fish | 7 | healthy | hamburguer |
F | tofu | 4 | healthy | hamburguer |
G | fish | 10 | healthy | hamburguer |
I would like Qlikview to generate two new columns (Classifications name the foods that the item_id are replacements for...), so, I would like to show for those foods that are displayed in the Classifications it adds in the line also the item_id for that food and the price, something like a lookup... The final output would be:
item_id | Food_name | Food_Price | Classification | Classifications | item_id1 | Food_name1 |
1 | hamburguer | 10 | unhealthy | |||
2 | ice cream | 11 | unhealthy | |||
3 | steak | 12 | unhealthy | |||
4 | bread | 13 | healthy | |||
5 | coke | 14 | unhealthy | |||
6 | juice | 1 | healthy | |||
7 | green salad | 2 | healthy | |||
8 | mango | 3 | healthy | |||
9 | ham | 15 | healthy | |||
10 | cesar salad | 16 | unhealthy | |||
11 | chicken | 8 | healthy | |||
12 | fish | 8 | healthy | |||
13 | tofu | 5 | healthy | |||
A | juice | 0.5 | healthy | coke | 5 | 14 |
B | green salad | 1.5 | healthy | cesar salad | 10 | 16 |
C | mango | 2.5 | healthy | ice cream | 2 | 11 |
D | chicken | 7 | healthy | hamburguer | 1 | 10 |
E | fish | 7 | healthy | hamburguer | 1 | 10 |
F | tofu | 4 | healthy | hamburguer | 1 | 10 |
G | fish | 10 | healthy | hamburguer | 1 | 10 |
how do I create that???
Dear Julio,
With Eduardo solution remember to drop the original_table (othewise you will deal with a lot synthetic keys).
I also used "NoConcatenate" in the attache sample.
/regards
How do I create those two extra columns?
Suppose your table is named 'original_table', insert the following in your script, after loading original_table:
left join (original_table)
load Food_name as Classifications,
item_id as item_id1,
Food_price as Food_name1
Resident original_table;
Eduardo
Dear Julio,
With Eduardo solution remember to drop the original_table (othewise you will deal with a lot synthetic keys).
I also used "NoConcatenate" in the attache sample.
/regards
Hi Joseph,
It's not necessary to drop the table since the table is being joined to itself. The result is the original table with the new two columns.
Eduardo
Hi Eduardo,
You are right in your case
I was used differenrt approach (2 tables) that is why I warned about synthetic keys.
Thank you for your views.