2 Replies Latest reply: Apr 18, 2017 7:02 AM by Mike Seisbye RSS

    add calculated field to interval match

    Mike Seisbye

      I have the following code.  which are looking up a tax percentage based on the invoice date. This tax i need to deduct from the cost.

       

      Rates:

      Directory;


      Mapping LOAD

          Company,

          Rate_GBP

      FROM

      [Basedata\Exchange Rates.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      Sales:

       

      LOAD

          InvNumber,

          InvoiceDate as CalendarDate,

          InvMonth,

          InvYear,

          Company,

          OrderNumber,

          OrderType,

          OrderLineNumber,

          OrderDate,

          SellToCustomer,

          ItemNumber,

          NettLocal as [Sales LCY AC],

          NettLocal / ApplyMap('Rates', Company, 0) as [Sales GBP AC],

          OrderCurrency,

          Cost,

          ([Cost]/[Markup Percentage]) as [Cost without tax],

          Nettlocal - cost without tax  as margin

          more stuff with the cost without tax.

          [Local Curr]

             

      FROM

      [Sales.qvd]

      (qvd) Where Len(InvNumber)>0;

       

      Intervals:

       

      LOAD

          Collroll_From,

          Collroll_To,

          [Markup Percentage],

          CR_Company,

          CR_Country,

          CR_Country &'-'& Collroll_From as [Costroll Name]

      FROM

      [Markup Intervals - DK TEST.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      IntervalMatch:

      IntervalMatch (CalendarDate)

      Load distinct Collroll_From, Collroll_To resident Intervals;


      Right now i'm doing it this way. (without the red text).  this will calculate the correct amounts if i add enough dimensions. But it's only right per line in a straight table. not the totals etc.

       

      =sum({$<CalendarYear = {$(=max(CalendarYear))}>} [Sales LCY AC])- (sum({$<CalendarYear = {$(=max(CalendarYear))}>} [cost])/ sum({$<CalendarYear = {$(=max(CalendarYear))}>} [Markup Percentage]))


      It's not really working as intended.  Also i'm having more problems with it.  I need further fields in the load script, to simplify the formulas.  So i'm thinking I need to create the Red lines in the load script.  which would make it possible to sum on that one field. and not create it by dividing subtracting etc. in the charts.

       

      But my problem are i want to load the fields in the sales table, but this is not possible since the markup aren't interval match before the sales load are done.  so how would i achieve this?  Also to complex it a bit. I need to add the country to the interval match. right now i'm only testing with 1 country.  But in fact i have 4 countries, with different tax'es and different dates.

       

      Should I drop the interval match and go another way? or how can i move forward.  

       

      Thank you very much

        • Re: add calculated field to interval match
          Malini Chandrasekar

          Hello Mike,

          If my understanding is correct, you would want to use the field [Markup Percentage] in Sales table to calculate new fields marked in red.

           

          You could use Extended Interval Match to bring in the Fields from Intervals table into Sales table. I have included Country level to intervals so use can match this to your script.

           

          Intervals:

          Load * Inline

          [

          Collroll_From,Collroll_To,Markup Percentage,Country

          01/01/2017,31/01/2017,30,US

          01/02/2017,28/02/2017,25,US

          01/03/2017,31/03/2017,15,US

          01/04/2017,30/04/2017,32,US

          ];

           

          Sales:

          Load * Inline

          [

          InvoiceNo,CalendarDate,Cost,Country

          111,10/01/2017,200,US

          112,02/02/2017,350,US

          113,21/03/2017,400,US

          114,11/04/2017,120,US

          ];

           

          Left Join IntervalMatch(CalendarDate,Country)

          Load Collroll_From,Collroll_To,Country Resident Intervals;

           

          Left Join(Sales)

          Load

          Collroll_From,Collroll_To,[Markup Percentage],Country

          Resident Intervals;

          Drop Table Intervals;

           

           

          Attached is the sample application for the same