topic Re: Creating a comparison loop in QlikView

Creating a comparison loop

Sat, 22 Feb 2014 13:22:52 GMT

Guys, I need a loop that compares the following 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 replacement
B	green salad	1.5	healthy	cesar salad replacement
C	mango	2.5	healthy	ice cream replacement
D	chicken	7	healthy	hamburger replacement
E	fish	7	healthy	hamburger replacement
F	tofu	4	healthy	hamburger replacement
G	fish	10	healthy	steak replacement

I need "Food_name" to be compared with "Classifications", for example, items D,E, and F are replacements for item 1 "Hamburguer". Not sure if I can take off the final "replacement" directly in my database, that's why, by all means, i need another formula, something like "Excluded left/right"... Can someone tell me how can I make this comparison looping? or any other thing that would solve this problem???

Re: Creating a comparison loop

Sat, 22 Feb 2014 13:59:29 GMT

Hi Julio,

even having some questions I tried to point into right direction.

a) loading source data into RawData

b) creating mapping table for the replacements(I choosed chicken as candidate for meet)

c) loading again using function ApplyMap(), doing the replacement via Mapping Table

RawData:

LOAD * Inline [

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

];

ReplMap:

Mapping

LOAD * Inline [

From, Into

coke,juice

cesar salad,green salad

ice cream,mango

hamburguer,chicken

steak,fish

];

ResultTab:

LOAD

item_id,

ApplyMap('ReplMap',Food_name,Food_name) AS Food_name,

Food_Price,Classification

Resident RawData;

DROP Table RawData;

hth

Roland

Re: Creating a comparison loop

Sat, 22 Feb 2014 14:18:23 GMT

Thank you Roland, but this is not exactly what I would like to have...

I also have this other table:

Customer	item_id
Carmen	1
Carmen	2
Carmen	5
Carmen	7
Carmen	9
Betty	10
Betty	2
Betty	3
Lindsey	1
Lindsey	4
Joseph	7
Joseph	8
Joseph	6
Sergio	3
Sergio	1
Sergio	14

So, Carmen had item_id=1 "hamburguer". When I choose that in qlikview, I would like to have the option "hamburguer replacement", and when I click on that hamburguer replacement, I would like to see the items that replaces the hamburguer, (items_id 12, 13 and 14). By the end, I choose one of the three items, for example, "Fish", and then I get "Carmen|Fish|fish_price|"... Like that...

But Thank you Roland