Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Employee
Employee

Re: Scripting new columns from original table (Lookup)

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

6 Replies
Not applicable

Re: Scripting new columns from original table (Lookup)

How do I create those two extra columns?

eduardo_sommer
Valued Contributor

Re: Scripting new columns from original table (Lookup)

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

Employee
Employee

Re: Scripting new columns from original table (Lookup)

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
Valued Contributor

Re: Scripting new columns from original table (Lookup)

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

Employee
Employee

Re: Scripting new columns from original table (Lookup)

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.

anbu1984
Honored Contributor III

Re: Scripting new columns from original table (Lookup)

Community Browser