Calculation possible in Scripting?

[#1]

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

[#2]

Hi,

you can use the below.

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

[#3]

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

[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.

[#5]

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

[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)

[#7]

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

[Peter_Cammaert · Accepted solution]

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

[#9]

HI Peter,

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

BR,

[#10]

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