Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating a comparison extra field for my table

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_idFood_nameFood_PriceClassificationReplacement for
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
Dchicken7healthyhamburger
Efish7healthyhamburger
Ftofu4healthyhamburger
Gfish10healthysteak

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

item_idFood_nameFood_PriceClassificationitem_id_ReplacementReplacement for:
1hamburguer10unhealthy
2ice cream11unhealthy
3steak12unhealthy
4bread13healthy
5coke14unhealthy
6juice1healthy
7green salad2healthy
8mango3healthy
9ham15healthy
10cesar salad16unhealthy
11chicken8healthy
12fish8healthy
13tofu5healthy
Ajuice0.5healthy5coke
Bgreen salad1.5healthy10cesar salad
Cmango2.5healthy2ice cream
Dchicken7healthy1hamburger
Efish7healthy1hamburger
Ftofu4healthy1hamburger
Gfish10healthy3steak
1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Please check enclosed file.

Hope this helps...

View solution in original post

7 Replies
MK_QSL
MVP
MVP

Please check enclosed file.

Hope this helps...

Not applicable
Author

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

Not applicable
Author

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

Didn't understand your problem.

Can you elaborate?

Not applicable
Author

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

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

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

Not applicable
Author

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