topic Re: Scripting new columns from original table (Lookup) in QlikView

Scripting new columns from original table (Lookup)

Sat, 10 May 2014 23:03:09 GMT

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???

Re: Scripting new columns from original table (Lookup)

Sat, 10 May 2014 23:03:28 GMT

How do I create those two extra columns?

Re: Scripting new columns from original table (Lookup)

eduardo_sommer — Sat, 10 May 2014 23:31:25 GMT

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

Re: Scripting new columns from original table (Lookup)

Joseph_Musekura — Sun, 11 May 2014 06:30:32 GMT

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

Re: Scripting new columns from original table (Lookup)

eduardo_sommer — Sun, 11 May 2014 13:24:35 GMT

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

Re: Scripting new columns from original table (Lookup)

Joseph_Musekura — Sun, 11 May 2014 13:31:42 GMT

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.

Re: Scripting new columns from original table (Lookup)

anbu1984 — Mon, 12 May 2014 09:27:43 GMT

Re: Scripting problem in relating columns in the same table.