Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
I will try this option
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.
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
Thank you so much !! Jason This option Works Fine