11 Replies Latest reply: Oct 29, 2014 2:29 AM by Avinash Jagilenki RSS

    Calculation possible in Scripting?

      Hi All,

       

      Lets say i have following columns

       

      Date1  Date2  period  Product  Value

       

      Now i want to write in script so that it will find me the difference of Value of two different dates.

       

      Lets say

      Date1         Date2           period         Product      Value

      1/8/2014     1/8/2014         02              A             10

      1/8/2014     20/8/2014        03              A             30

      1/8/2014     25/08/2014      02               A             15

      20/8/2014    20/8/2014       02              A               40

      20/8/2014    17/9/2014       01              B               20

       

       

      so now script sould find out if the two dates are equal

      then  (Value of (thatday+19 days) - (value on that day )

       

      To be more clear when my script finds 1/8/2014  = 1/8/2014 then the output should be

      ( value on 20/08/2014) - ( value on 01/08/2014)

      so O/P is 40 - 10 = 30... This will apply for other dates as well. Please help

       

      Br,

      Avi

        • Re: Calculation possible in Scripting?

          Hi,

          you can use the below.

          if(Date1=Date2,sum(Value)-sum({<Date2={'$(=$(Date2-19))'}>}Value)

          • Re: Calculation possible in Scripting?
            Peter Cammaert

            Just a question about the intended logic:

             

            The value on (thatday + 19 days), should it be the value in the record with Date1 = Date 2?

             

            As an example, imagine I have a record for product A where Date 1 = 13/7/2014 and Date 2 = 13/7/2014. Now there are three possibilities for value of (thatday + 19 days)

             

            An easy solution could be with a mapping table.

              • Re: Calculation possible in Scripting?

                HI Peter,

                 

                May be i might have put in wrong way.

                 

                Thatday in my sentence means the Date1 where Date1 = date2.

                 

                So i mean Thatday is Date1.

                 

                To be more clear when my script finds 1/8/2014  = 1/8/2014 then the output should be

                ( value of when Date1 +19 = Date2 ) - ( value on 01/08/2014 = 1/8/2014 ). 

                Date1 +19 is  20/08/2014. There will be only one occurence where 20/08/2014= 20/08/2014(Date1 = Date2)

                The value on 20/08/2014 is 40 and The value on 01/08/2014 is 10 hence O/P is 40 - 10 = 30.

                 

                Am i clear ?

                 

                Please let me know if anything is confusing

                  • Re: Calculation possible in Scripting?
                    Peter Cammaert

                    I do understand that logic. What isn't clear to me is what happens if (thatday + 19 days) results in multiple possible values.

                     

                    Please revisit my example. Imagine I add a record at the beginning of your table like the following:

                     

                    Date 1          Date 2       period  Product  Value

                    13/7/2014    13/7/2014   02       A             5

                     

                    Clearly Date 1 = Date 2, so your expression applies. But Thatday+19 results in 3 records for the same product with different values. What do we do? Sum all values (value = 55)? Take the one where Date 1 = Date 2 (value = 10)

                      • Re: Calculation possible in Scripting?

                        HI Peter,

                         

                        There will be only one occurence where Date1 = Date2 for a specific date. For a combination of Date1, Date2, period, Product

                        So i wont be having data like Date1 = Date2 for 3 times for a specific date for a combination.

                         

                        So 13/7/2014 = 13/7/2014 occurence will be only once for the combination of 

                         

                        Date 1          Date 2       period  Product

                        13/7/2014    13/7/2014   02       A      

                         

                        There can be another line where

                         

                        Date 1          Date 2       period  Product  Value

                        13/7/2014    13/7/2014   02       B               50

                         

                        Then this record should search for    

                        Date 1          Date 2       period  Product  Value

                        1/08/2014    1/08/2014   02       B               100

                         

                        And give output of 100- 50 = 50

                         

                        It shouldnot search for the record of

                        Date 1          Date 2       period  Product  Value

                        1/08/2014    1/08/2014   02        A             100

                         

                        Because Product is A. The above record should be matched with

                         

                        Date 1          Date 2       period  Product  Value

                        13/07/2014    13/07/2014   02        A             100

                         

                        Hope this has clarrified the doubt. If you still have any doubt please do let me know

                          • Re: Re: Calculation possible in Scripting?
                            Peter Cammaert

                            All clear.

                             

                            How about adding something like this:

                             

                            MapDate2Value19DaysOn:

                            MAPPING LOAD Date1 &'|' & period & '|' & Product as A, Value AS B

                            RESIDENT YourOriginalFactsTable

                            WHERE Date1 = Date2;

                             

                            NewFactsTable:

                            LOAD Date1, Date2, period, Product,

                                 IF (Date1 <> Date2, Value,

                                   applymap('MapDate2Value19DaysOn', date(Date1 + 19) & '|' & period & '|' & Product, 0) - Value) AS Value

                            RESIDENT YourOriginalFactsTable;

                             

                            Best,

                             

                            Peter

                              • Re: Calculation possible in Scripting?

                                HI Peter,

                                 

                                ThankYou I will try on the suggested and will get back to you

                                 

                                BR,

                                • Re: Calculation possible in Scripting?

                                  HI Peter,

                                   

                                  It is working fine but

                                  i am using one more logic where it is failing.

                                   

                                  MapDate2Value:

                                  MAPPING LOAD Date1 &'|' Date2 &'|'& period & '|' & Product   as C, Value AS D

                                  RESIDENT YourOriginalFactsTable;

                                   

                                  NewFactsTable:

                                  LOAD Date1, Date2, period, Product,

                                       IF (Date1 <> Date2, Value,

                                         applymap('MapDate2Value', Date1 &'|' date(Date1 + 7) & '|' & period & '|' & Product, 0) - Value) AS Value

                                  RESIDENT YourOriginalFactsTable;


                                  The above logic is for :


                                  My Data

                                   

                                  Date1         Date2           period         Product      Value

                                  1/8/2014     1/8/2014         02              A             10

                                  1/8/2014     20/8/2014        03              A             30

                                  1/8/2014     25/08/2014      02               A             15

                                   

                                  Logic is maintained so that it should give 30-10 = 20 , 15-10 = 5

                                   

                                  Am i doing some wrong or what i am not understanding why it is not matching the records . The output I am getting is 0- Value(Because it is failing to match the recors)

                                  IS there any limitation for Applymap?

                                   

                                   

                                  BR

                                    • Re: Calculation possible in Scripting?
                                      Peter Cammaert

                                      Two thoughts:

                                       

                                      • In the NewFactsTable LOAD, you're missing an ampersand in the second parameter expression, between '|' and date()
                                      • If I read your new script correctly, you're trying to get hold of values in records that have Date2 = Date1 + 7 days, right? For the example data, this will fail because there is no record where Date1 = 1/8/2014 and Date2 = 8/8/2014.

                                       

                                      You can obtain the required result if you reverse the logic for records with Date1 <> Date2. Do not keep Value-as-is, but calculate the new value as follows: Value - applymap('MapDate2Value', Date1 & '|' & period & '|' & Product, 0) Or with the complete context:


                                      MapDate2Value19DaysOn:

                                      MAPPING LOAD Date1 &'|' & period & '|' & Product as A, Value AS B

                                      RESIDENT YourOriginalFactsTable

                                      WHERE Date1 = Date2;

                                       

                                      NewFactsTable:

                                      LOAD Date1, Date2, period, Product,

                                           IF (Date1 <> Date2,

                                             Value - applymap('MapDate2Value', Date1 & '|' & period & '|' & Product, 0),

                                             applymap('MapDate2Value19DaysOn', date(Date1 + 19) & '|' & period & '|' & Product, 0) - Value) AS Value

                                      RESIDENT YourOriginalFactsTable;

                                       

                                       

                                      Best,

                                       

                                      Peter