Creating a comparison extra field for my table

[#1]

So, i have this table that I need to compare column "Food_name", and "Replacement for"

What I need:

everytime I pick one item from the field "Replacement for", it returns to me the item_id from the Food_name related to, and i want that to be stored as a field... For example:

If I pick any of the items that is a replacement for hamburguer, it should return to me the id = 1... So, If I choose fish, which is a replacement for hamburguer, it returns me the item_id 1...

The table is this one:

item_id	Food_name	Food_Price	Classification	Replacement for
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	hamburger
E	fish	7	healthy	hamburger
F	tofu	4	healthy	hamburger
G	fish	10	healthy	steak

The final table I would like to have it's this one, with that "item_id_Replacement" added created by qlikview :

item_id	Food_name	Food_Price	Classification	item_id_Replacement	Replacement for:
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	5	coke
B	green salad	1.5	healthy	10	cesar salad
C	mango	2.5	healthy	2	ice cream
D	chicken	7	healthy	1	hamburger
E	fish	7	healthy	1	hamburger
F	tofu	4	healthy	1	hamburger
G	fish	10	healthy	3	steak

[MK_QSL · Accepted solution]

Please check enclosed file.

Hope this helps...

[#3]

Julio,

I think you can use Applymap() function.

First, load a table with two fields (Food_Name, id): first field is the key, second field is the one that will be the replacement

MapFood:

Mapping LOAD

Food_Name, item_id

....

And afterwars, use Applymap():

LOAD ...

Applymap('MapFood', Replacement_for) as newid,

.....

MapFood is a temp table. No need to drop it at the end of the script.

Fabrice

[#4]

thank you both... just to finish my questions... When I get the field in the dropbox, i get also the blanks fields also... How do I take that off???

[MK_QSL]

Didn't understand your problem.

Can you elaborate?

[#6]

in the qlikview, I go "select fields", then I choose the field "Replacement for", the blank fields are coming, i want that not to come with... where do I take that off?

[MK_QSL]

Right below expression in List Box Expression instead of selecting [Replacement for] directly...

=IF(Len([Replacement for])>0,[Replacement for])

[#8]

thank you alot, that's exactly what i wanted (: