Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

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

1 Solution

Accepted Solutions
Partner
Partner

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

6 Replies
Partner
Partner

Accummulated Null Problem

Can this post set you on your way?

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

Jason

Partner
Partner

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

Not applicable

Accummulated Null Problem

I will try this option

Not applicable

Accummulated Null Problem

Jason

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

YearMonthAccountValue
201011010100
201021010200
201031010300
201041010400
201051010500
201011020100
201021020200
201031020300
201041020400
201051020500
201061020600

And another temporal caldendar table with this

YearMonth
20101
20102
20103
20104
20105
20106
20107
20108
20109
201010
201011
201012

The LEFT JOINED  TABLE

AccountKeyValueAcum_Value
10102010-1100100
10102010-2200300
10102010-3300600
10102010-44001000
10102010-55001500
10202010-1100100
10202010-2200300
10202010-3300600
10202010-44001000
10202010-55001500
10202010-66002100
2010-7
2010-8
2010-9
2010-10
2010-11
2010-12

Pleas can you post an example.

Partner
Partner

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

Not applicable

Accummulated Null Problem

Thank you so much !! Jason This option Works Fine