4 Replies Latest reply: Jun 6, 2016 4:46 AM by Robin Luiten RSS

    Join statement for non-matching values

    Robin Luiten

      Hi everyone,

       

      I've started this discussion in hope you guys can help me with the following issue. I have a customer database wherein customer discounts are assigned to product groups. I want to match these customer discounts with the product groups in our item database. Therefore I am looking for a join command.

       

      As you can see in the example hereunder I have three digit product groups in the item database. My customer database does not have a discount for every three digit product group. So a left join or a inner join would return only three rows with a discount. However, I do want to have a discount for every product group. Therefore I want to assign the discount in the two digit product group for each three digit product group that I can't match.

       

      Examples and the desired result are stated below. I would really appreciate your help.

       

      Kind regards,

       

      Robin

       

      Item database
      Product group
      Group.100
      Group.101
      Group.102
      Group.103
      Group.104
      Group.105
      Group.106

       

      -join statement-

       

      Customer database
      Product groupDiscount %
      Group.1020
      Group.10030
      Group.10230
      Group.10630

       

      -gives-

        

      Result
      Product group

      Discount %

      Group.10030
      Group.10120
      Group.10230
      Group.10320
      Group.10420
      Group.10520
      Group.10630
        • Re: Join statement for non-matching values
          Settu Periyasamy

          Try this..

           


          CustomerDataBase:
          Mapping
          LOAD * INLINE [
          Product group, Discount %
          10, 20
          100, 30
          102, 30
          106, 30
          ]
          ;

          ItemDataBase:
          LOAD [Product group],ApplyMap('CustomerDataBase',[Product group],
          ApplyMap('CustomerDataBase',Left([Product group],2),'Unknown%')) as Discount%;
          LOAD * INLINE [
          Product group
          100
          101
          102
          103
          104
          105
          106
          ]
          ;

            • Re: Join statement for non-matching values
              Robin Luiten

              Hello Settu,

               

              Thank you for your reply. Your code works for the given example, only in practice we have over 300 different product groups with discounts assigned for over 1000 customer records. So the real database is a little more comprehensive then I illustrated. Discounts are assigned to the product groups in hierarchical order:

               

              Three digit product group discount, if no discount assigned, then

              -> Two digit product group discount, if no discount assigned, then

                  ->  One digit product group discount, if no discount assigned, then

                       -> Null

               

              Is there a way to use the mapping statement (or any other statement) without defining every single value?

               

              Thanks!

                • Re: Join statement for non-matching values
                  Settu Periyasamy

                  Still you can use the multiple Apply map based on your hirarchy count.. can you post the sample which is illustrate your original issue?

                    • Re: Join statement for non-matching values
                      Robin Luiten

                      Hello Settu,

                       

                      Couldn't get to you sooner because of my vacation. I will make an effort to illustrate my problem better.

                       

                      Customer table with discounts per product group:

                      CustomerProduct groupDiscount
                      1110
                      11020
                      110130
                      110230
                      110330
                      2117
                      210150
                      210250
                      210350
                      3115
                      31025
                      310130
                      310230
                      310330

                       

                      Result after assigning customer discounts to product groups in our item database.

                      CustomerProduct groupApplied discount
                      110020
                      110130
                      110230
                      110330
                      110420
                      110520
                      210017
                      210150
                      210250
                      210350
                      210417
                      210517
                      3115
                      310025
                      310130
                      310230
                      310330
                      310425
                      310525

                       

                      Customer 1 as example. Where there is no three-digit product group discount known, the two-digit product group discount is applied. Therefore the assigned discount for product groups 100, 104 and 105 values 20.

                       

                      Customer 2 as example. Where there is no three-digit-discount known, the two-digit-discount is applied. However, in case of customer 2 there is no two-digit-discount known. Therefore discount of the one-digit product group discount is applied for product groups 100, 104 and 105, which values 17.

                       

                      This is just an example. Our database is much more comprehensive so I am looking for a geneal logical (join?) statement to get the discount appropriately assigned, rather than a mapping statement.

                       

                      Thanks in advance!