2 Replies Latest reply: Feb 22, 2014 9:18 AM by Julio Lourenco RSS

    Creating a comparison loop

      Guys, I need a loop that compares the following table:

       

      item_idFood_nameFood_PriceClassificationClassifications
      1hamburguer10unhealthy
      2ice cream11unhealthy
      3steak12unhealthy
      4bread13healthy
      5coke14unhealthy
      6juice1healthy
      7green salad2healthy
      8mango3healthy
      9ham15healthy
      10cesar salad16unhealthy
      11chicken8healthy
      12fish8healthy
      13tofu5healthy
      Ajuice0.5healthycoke replacement
      Bgreen salad1.5healthycesar salad replacement
      Cmango2.5healthyice cream replacement
      Dchicken7healthyhamburger replacement
      Efish7healthyhamburger replacement
      Ftofu4healthyhamburger replacement
      Gfish10healthysteak 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

          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

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

               

              I also have this other table:

               

              Customeritem_id
              Carmen1
              Carmen2
              Carmen5
              Carmen7
              Carmen9
              Betty10
              Betty2
              Betty3
              Lindsey1
              Lindsey4
              Joseph7
              Joseph8
              Joseph6
              Sergio3
              Sergio1
              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