Searching the right customer in the tables to provide possible choices

[#1]

Hey guys, I have this two tables:

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

Classifications means that the food is replacement for that (ie. item_id = A , juice is a replacement for Classifications = coke),

I have another table with the customer orders;

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

For example: Carmen ordered item_id = 1 (hamburguer)

I would like to have a table that shows healthy food that has replacements, the customers that have ordered something unhealthy that could be replaced for that food, and customers all possible choices (like table below)

item_id	Food_name	Replacement	Customer	Food_Price	Saving
D	chicken	hamburguer replacement	Carmen	7	3
D	chicken	hamburguer replacement	Lindsey	7	3
D	chicken	hamburguer replacement	Sergio	7	3
E	fish	hamburguer replacement	Carmen	7	3
E	fish	hamburguer replacement	Lindsey	7	3
E	fish	hamburguer replacement	Sergio	7	3
G	fish	hamburguer replacement	Carmen	10	0
G	fish	hamburguer replacement	Lindsey	10	0
G	fish	hamburguer replacement	Sergio	10	0
F	tofu	hamburguer replacement	Carmen	4	6
F	tofu	hamburguer replacement	Lindsey	4	6
F	tofu	hamburguer replacement	Sergio	4	6

I have created a Qlikview for this working script. This showing customer part is the one not working properly. Could you guys help me out with that?

[#2]

The table is called "Final", the one that I would like to have it working...

[SergeyMak]

Hi Julio,

You just need to join table to it self

[Products]:

LOAD

item_id,
Food_name,
Food_Price,
Classification,
Classifications
FROM .... ;

LEFT JOIN (Products)

Classifications AS Food_Name,

Food_Name AS Replacement

Resident Product;

Then you will be able to do what you need.

[#4]

I didn't get quite right this part...

The thing is, in fact, the customers did not ordered the foods in the Final table, but that food is a possible replacement for that customer... So, I wanted to see a table with the customer's possible choices of healthy food...

How do I achieve that?

[#5]

Anyone?

[#6]

Hello Julio,

can customer order positions with item_id A-G or those positions are only references for substitutes?

Why price of juice with 6 and A item_id is different?

Regards

Darek

[#7]

Those item_id are references to the Foods...

This is just a different kind of juice, I made some tests using the same Food_names, because sometimes we might have equal names, and this script cannot fail in this case also...

Customers can order anything they want from 1-13 and A-G... It's just that A-G they can be a replacement for a unhealthy food...

I want that final table to show the possibility that each customer have to order that food. There is a picture that would ilustrate that:

[#8]

i think this script should work for you:

LOAD Customer,

    item_id

FROM

(biff, embedded labels, table is Arkusz2$);

LOAD item_id,

    Food_name,

    Food_Price,

    Classification

FROM

(biff, embedded labels, table is Arkusz1$);

LOAD Food_name as replacement,

    Food_Price as replacement_price,

    Classifications as Food_name

FROM

(biff, embedded labels, table is Arkusz1$);