Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Scripting new columns from original table (Lookup)

Hey Guys,

I have this table:

item_idFood_nameFood_PriceClassificationClassifications
1hamburguer10unhealthy
2ice cream11unhealthy
3steak12unhealthy
4bread13healthy
5coke14unhealthy
6juice1healthy
7green salad2healthy
8mango3healthy
9ham15healthy
10cesar salad16unhealthy
11chicken8healthy
12fish8healthy
13tofu5healthy
Ajuice0.5healthycoke
Bgreen salad1.5healthycesar salad
Cmango2.5healthyice cream
Dchicken7healthyhamburguer
Efish7healthyhamburguer
Ftofu4healthyhamburguer
Gfish10healthyhamburguer

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_idFood_nameFood_PriceClassificationClassificationsitem_id1Food_name1
1hamburguer10unhealthy
2ice cream11unhealthy
3steak12unhealthy
4bread13healthy
5coke14unhealthy
6juice1healthy
7green salad2healthy
8mango3healthy
9ham15healthy
10cesar salad16unhealthy
11chicken8healthy
12fish8healthy
13tofu5healthy
Ajuice0.5healthycoke514
Bgreen salad1.5healthycesar salad1016
Cmango2.5healthyice cream211
Dchicken7healthyhamburguer110
Efish7healthyhamburguer110
Ftofu4healthyhamburguer110
Gfish10healthyhamburguer110

how do I create that???

1 Solution

Accepted Solutions
Joseph_Musekura
Support
Support

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

View solution in original post

6 Replies
Not applicable
Author

How do I create those two extra columns?

eduardo_sommer
Partner - Specialist
Partner - Specialist

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

Joseph_Musekura
Support
Support

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

eduardo_sommer
Partner - Specialist
Partner - Specialist

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

Joseph_Musekura
Support
Support

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.