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

# 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?

HI,

Can you tell me why  you have used Sum function and also can you tell me the logic which you have given can be written in the script ? I want to implement in Script not in the front end

• ###### Re: Calculation possible in Scripting?

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?

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?

All clear.

MapDate2Value19DaysOn:

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

RESIDENT YourOriginalFactsTable

WHERE Date1 = Date2;

NewFactsTable:

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:

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?

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:

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

• ###### Re: Calculation possible in Scripting?

HI Peter,

I found the issue.

It is working fine when did some format to the dates.

Thanks for your support and the help

Br