Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Searching the right customer in the tables to provide possible choices

Hey guys, I have this two tables:

item_idFood_nameFood_PriceClassificationClassifications
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
Dchicken7healthyhamburguer
Efish7healthyhamburguer
Ftofu4healthyhamburguer
Gfish10healthyhamburguer

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;

Customeritem_id
Carmen1
Carmen2
Carmen5
Carmen7
Carmen9
Betty10
Betty2
Betty3
Lindsey1
Lindsey4
Joseph7
Joseph8
Joseph6
Sergio3
Sergio1

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_idFood_nameReplacementCustomerFood_PriceSaving
Dchickenhamburguer replacementCarmen73
Dchickenhamburguer replacementLindsey73
Dchickenhamburguer replacementSergio73
Efishhamburguer replacementCarmen73
Efishhamburguer replacementLindsey73
Efishhamburguer replacementSergio73
Gfishhamburguer replacementCarmen100
Gfishhamburguer replacementLindsey100
Gfishhamburguer replacementSergio100
Ftofuhamburguer replacementCarmen46
Ftofuhamburguer replacementLindsey46
Ftofuhamburguer replacementSergio46

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?

7 Replies
Not applicable

Re: Searching the right customer in the tables to provide possible choices

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

sergey_maka8
Valued Contributor

Re: Searching the right customer in the tables to provide possible choices

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.

Not applicable

Re: Searching the right customer in the tables to provide possible choices

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?

Not applicable

Re: Searching the right customer in the tables to provide possible choices

Anyone?

Not applicable

Re: Searching the right customer in the tables to provide possible choices

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

Not applicable

Re: Searching the right customer in the tables to provide possible choices

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:

Final table.png

Not applicable

Re: Re: Searching the right customer in the tables to provide possible choices

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$);

Community Browser