7 Replies Latest reply: May 14, 2014 8:35 AM by Dariusz Mielczarek RSS

    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?