topic Accummulated Null Problem in QlikView

Accummulated Null Problem

Tue, 03 Apr 2012 17:00:02 GMT

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

Jason_Michaelides — Tue, 03 Apr 2012 17:07:45 GMT

Can this post set you on your way?

http://community.qlik.com/thread/50519

Jason

Accummulated Null Problem

Jason_Michaelides — Tue, 03 Apr 2012 17:10:06 GMT

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

Tue, 03 Apr 2012 17:36:05 GMT

I will try this option

Accummulated Null Problem

Tue, 03 Apr 2012 18:06:14 GMT

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

Jason_Michaelides — Tue, 03 Apr 2012 20:08:02 GMT

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

Wed, 04 Apr 2012 00:24:56 GMT

Thank you so much !! Jason This option Works Fine