6 Replies Latest reply: Apr 3, 2012 8:24 PM by Marcos Herrera

# Accummulated Null Problem

Dear Community

I have a script with the function code that builds good peek account, company, location, year and month, but I have a problem when the value is null in a month. An Example

Account,Company,Location,Year,Month,Value,Value_Acum

1010,A,A1,2010,1,100,100

1010,A,A1,2010,2,200,300

1010,A,A1,2010,3,300,600

1010,A,A1,2010,4,400,1000

1010,A,A1,2010,5,500,1500

1010,A,A1,2010,6,600,2100

1020,A,A1,2010,1,100,100

1020,A,A1,2010,2,200,300

1020,A,A1,2010,3,300,600

1020,A,A1,2010,4,400,1000

1020,A,A1,2010,5,500,1500

1020,A,A1,2010,6,600,2100

1020,A,A1,2010,7,700,2800

1020,A,A1,2010,8,800,3600

1020,A,A1,2010,9,900,4500

1020,A,A1,2010,10,1000,5500

1020,A,A1,2010,11,1100,6600

1020,A,A1,2010,12,1200,7800

If I want know the Value_Acum per year 2012, month 12, account 1010 there aren't. This is my problem. i need know how complete the missing months.

example:

1010,A,A1,2010,7,0,2100

1010,A,A1,2010,8,0,2100

1010,A,A1,2010,9,0,2100

1010,A,A1,2010,10,0,2100

1010,A,A1,2010,11,0,2100

1010,A,A1,2010,12,0,2100

• ###### Accummulated Null Problem

Can this post set you on your way?

Jason

• ###### Accummulated Null Problem

Or if you know you want a row for every month between 2 dates then generate a calendar table of all months and left join your table into it before you calculate your Accum field. Then load the resident table again, this time adding the Accum field.

Hope this helps,

Jason

• ###### Accummulated Null Problem

I will try this option

• ###### Accummulated Null Problem

Jason

In the LEFT JOIN option it's not clear for me the JOINED Final table i tried this

 Year Month Account Value 2010 1 1010 100 2010 2 1010 200 2010 3 1010 300 2010 4 1010 400 2010 5 1010 500 2010 1 1020 100 2010 2 1020 200 2010 3 1020 300 2010 4 1020 400 2010 5 1020 500 2010 6 1020 600

And another temporal caldendar table with this

 Year Month 2010 1 2010 2 2010 3 2010 4 2010 5 2010 6 2010 7 2010 8 2010 9 2010 10 2010 11 2010 12

The LEFT JOINED  TABLE

 Account Key Value Acum_Value 1010 2010-1 100 100 1010 2010-2 200 300 1010 2010-3 300 600 1010 2010-4 400 1000 1010 2010-5 500 1500 1020 2010-1 100 100 1020 2010-2 200 300 1020 2010-3 300 600 1020 2010-4 400 1000 1020 2010-5 500 1500 1020 2010-6 600 2100 2010-7 2010-8 2010-9 2010-10 2010-11 2010-12

Pleas can you post an example.

• ###### Re: Accummulated Null Problem

Sorry - I wasn't quite right and certainly wasn't clear.  Please see attached qvw for how to achieve your goal.  There is full commenting in the script.

Hope this helps,

Jason

• ###### Accummulated Null Problem

Thank you so much !! Jason This option Works Fine