1 Reply Latest reply: Sep 18, 2016 5:36 PM by Stefan Wühl RSS

    How to create intervalmatch

    Jim Chan

      Hi guys,

       

      I need to create a intervalmatch for employee Yearly Sales Target.

       

      So i have created Inline Data

       

      Interval_Sales_Target_Percentage_Grade:

      load * inline

      [

      Year,Min_Sales ,Max_Sales,Grade

      2015,85.01,95, Average

      2015,95.01,100,Good

      2015,100.01,999.9,Excellent

      ];

       

      and

       

      IntervalMatch:

      IntervalMatch (Sales)

      Load distinct Min_Sales, Max_Sales

      Interval_Sales_Target_Percentage_Grade;

       

      Problem is... i think the intervalmatch should not be "Sales". My orders and sales_target table is 2 diff table. But i think i need to create an expression, such as (total_sales/target)*100 as percentage_achieved. Then in my intervalmatch table, i should interval match(percentage_achieved).

       

      both tables rite now are linked with orders_key.

       

      target.jpg

       

      Rgds

       

      Jim

        • Re: How to create intervalmatch
          Stefan Wühl

          You could use a mapping table approach (using MAPPING LOAD prefix and ApplyMap() ) or a JOIN to create a single table with Total_Sales and Target values. Then do a resident LOAD to create your percentage column and use IntervalMatch() to match against your grades (maybe using year as key field in the IntervalMatch() extended syntax?).