LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: James M

Hi,

I have a bit of a problem with trying to match up adjustments made to customer's accounts.

This is what the files look like:

Adjustment File:
account adjustment year_month Type
111111111111 52.95 2014-3 Data
111111111111 62.95 2014-4 Data
222222222222 39.00 2014-4 Usage
333333333333 45.00 2014-3 Usage
333333333333 89.00 2014-4 Data

Customer File
account charge year_month
111111111111 52.89 2014-3
111111111111 62.95 2014-4
222222222222 39.15 2014-4
333333333333 44.00 2014-3
333333333333 89.00 2014-4

I want to wash 'Customer File' against 'Adjustment File' to see if any of the adjustments match to within .50. I then want to look-up the 'Type' of adjustment made

I've tried all sorts of things but still in the process of learning all the functionality of Lavastorm

Thanks
James

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dsc88dsc88

Hi James,

Below is an example of how you could achieve what you are looking for. Let me know whether this works for you.

Note: This logic only compares on the account number & month though - so if you had multiple account entries in the customer file for a given month then this would no longer provide clean results, e.g.
Customer File
account charge year_month
111111111111 52.89 2014-3
111111111111 62.95 2014-3


- - - - - - - - copy all text below this line into your graph - - - - - - - -

node:Customer_File
bretype:core::Static Data
editor:Label=Customer File
editor:sortkey=537b13762824729c_2
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
account:long,charge:double,year_month:string
111111111111,52.89,2014-3
111111111111,62.95,2014-4
222222222222,39.15,2014-4
333333333333,44.00,2014-3
333333333333,89.00,2014-4
EOX
editor:XY=160,180
end:Customer_File

node:Adjustment_File
bretype:core::Static Data
editor:Label=Adjustment File
editor:sortkey=537b13762824729c
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
account:long,adjustment:double,year_month:string,t ype:string
111111111111,52.95,2014-3,Data
111111111111,62.95,2014-4,Data
222222222222,39.00,2014-4,Usage
333333333333,45.00,2014-3,Usage
333333333333,89.00,2014-4,Data
EOX
editor:XY=160,100
end:Adjustment_File

node:Combine_and_compare
bretype:core::Join Left Inner
editor:Label=Combine and compare
editor:sortkey=537b143239810e5d
input:@45781ca80c2802d0/=Adjustment_File.40fe6c55598828e5
input:@45781ca971fe502f/=Customer_File.40fe6c55598828e5
output:@45781cad02e051b0/=
prop:LeftInputKey=<<EOX
account,year_month
EOX
prop:RightInputKey=<<EOX
account,year_month
EOX
prop:Script=<<EOX
value_difference = 1:adjustment - 2:charge
within_50_cents = if (value_difference <= 0.50 and value_difference >= -0.50) then "Y" else "N"

emit 1:account
emit 1:year_month
emit 1:type
emit 1:adjustment as adjustment_value
emit 2:charge as charge_value
emit value_difference
emit within_50_cents
EOX
prop:SortLeftInput=true
prop:SortRightInput=true
editor:XY=260,100
node:Sort
bretype:::Sort
editor:shadow=4578213227295e66
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Bypass_2
bretype:::Bypass
editor:shadow=4578213229d61611
input:@45782010749f0c96/=
input:@457820124fef7658/=
output:@40fd2c7436717256/=
end:Bypass_2

node:Sort_2
bretype:::Sort
editor:shadow=457821325111769a
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort_2

node:Join
bretype:::Join
editor:shadow=4578213269da7045
input:@40fd2c745b6d7704/=
input:@40fd2c74504921cd/=
output:@40fd2c7430f76546/=
end:Join

node:Bypass
bretype:::Bypass
editor:shadow=457821327950136b
input:@45782010749f0c96/=
input:@457820124fef7658/=
output:@40fd2c7436717256/=
end:Bypass

end:Combine_and_compare

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dsc88dsc88

James - for some reason there is a space in "Adjustment File" static node that I cannot remove on the post above. If you change...

account:long,adjustment:double,year_month:string,t ype:string

to

account:long,adjustment:double,year_month:string,type:string

then the above will work!

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: James M

Hi,

That worked perfectly, thank you very very much! I've never used the join nodes before, appreciate your help

Cheers,
James