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

[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

[#3]

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