topic Re: Help creating a pivot table in QlikView

Help creating a pivot table

goncalo_ricardo_pereira — Tue, 05 Nov 2013 19:18:36 GMT

Hello,

I´m trying to create one pivot table, but i´m having some problems...

I´m going to explain my problem using one example.

This is my pivot table right now:

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11
Value 1	0			3					8
Value 2	0			4				6

I'm trying to use the function before() or after(), but the only problem is that i can get only the last value or the next value, creating my table like this...

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11
Value 1	0	0		3	3				8	8
Value 2	0	0		4	4			6	6

This is the pivot table i´m trying to create.

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11
Value 1	0	0	0	3	3	3	3	3	8	8	8
Value 2	0	0	0	4	4	4	4	6	6	6	6

Is there any way to do this?

Thanks for the help.

Re: Help creating a pivot table

Tue, 05 Nov 2013 19:28:40 GMT

If you can put the file would help

Re: Re: Help creating a pivot table

Anonymous — Tue, 05 Nov 2013 19:34:09 GMT

Goncalo

As Octavio implied, it is pretty hard to guess what your underlying data really is and what you have done without you providing your qvw, but you could try this:

In your Chart [Pivot Table] Properties
Presentation tab
Bottom left hand corner you'll see
- Null Symbol
- Missing Symbol
  - Try setting one or both to 0

Best Regards, Bill

Re: Help creating a pivot table

MayilVahanan — Tue, 05 Nov 2013 19:45:34 GMT

Try in script itself like

Load *,

if ( Isnull(Value1), Peek('Value1'), Value1) as Value1

from tablename

Order by Week;

Re: Help creating a pivot table

goncalo_ricardo_pereira — Tue, 05 Nov 2013 20:06:26 GMT

Thanks for all your help.

I attach one example as you request.

This is the pivot table i´m trying to create.

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11
Value 1	0	0	0	3	3	3	3	3	8	8	8
Value 2	0	0	0	4	4	4	4	6	6	6	6

Yes is possible to do this in the script... but before that is there a way of doing this using only the expression of the pivot table?

Thank you for you help.

Re: Help creating a pivot table

Tue, 05 Nov 2013 20:23:22 GMT

Your problem is not the design of the table if the expression you are using.

could you tell me what you want with that expression you intend to represent

Re: Help creating a pivot table

goncalo_ricardo_pereira — Tue, 05 Nov 2013 21:01:26 GMT

Basically I´m trying to do one expression that allows me to fill all the 0 values with the last value > 0 presented in the same row.

For example:

in the first line (Value 1) between week 4 and week 9 i have:

week 4 week 5 week 6 week 7 week 8 week 9

3 0 0 0 0 8

In the week 5 to 8 i have 0, but in reality i want that week 5 to 8 get the same number of week 4, because its the previous value > 0.

If its possible using only the expression i will get this result:

week 4 week 5 week 6 week 7 week 8 week 9

3 3 3 3 3 8

Expression i´m trying to do:

if(columnno( ) = 1 and Sum(Value) <> 0, Sum(Value), get the previous value > 0)

Re: Help creating a pivot table

andrewpettit — Tue, 05 Nov 2013 23:34:20 GMT

Two suggestions that might be helpful. One it looks like your really trying to do a running total so have you tried using the "Full Accumulation"? Second perhaps Try "RangeSum( Before( [Week Col] ) , Sum( [ Week Val] ) )" the RangeSum will ensure that if the before column is null it will be treated as a "0" zero value and thus not cause the current calculation to return a null as well.

Re: Help creating a pivot table

goncalo_ricardo_pereira — Wed, 06 Nov 2013 00:16:49 GMT

Hello Andrew,

I´m not trying to do a "full accumulation". The "RangeSum" gives me one problem when i reach week 10 if in this week i have a value = 0:

using RangeSum example:

week 4 week 5 week 6 week 7 week 8 week 9 week 10

3 3 3 3 3 8 11

But in fact my desire result is:

week 4 week 5 week 6 week 7 week 8 week 9 week 10

3 3 3 3 3 8 8

Another example:

Lets imagine i have this values:

week 4 week 5 week 6 week 7 week 8 week 9 week 10 week 11 week 12

3 0 0 5 0 8 0 4 0

I´m trying to get an expression that gives me this result:

week 4 week 5 week 6 week 7 week 8 week 9 week 10 week 11 week 12

3 3 3 5 5 8 8 4 4

Basically, when i get a week that have a value = 0 i want to get the previous value > 0.

I already tried to use the rangemaxstring, but in this case i get this result:

week 4 week 5 week 6 week 7 week 8 week 9 week 10 week 11 week 12

3 3 3 5 5 8 8 4 8

And its wrong, because when i reach week 12 and the value = 0 i need the previous value > 0 and that value is in week 11 with a value = 4.

Once again thanks for your help

Re: Help creating a pivot table

andrewpettit — Thu, 07 Nov 2013 19:57:49 GMT

so maybe what you need is more like this?

if( RangeSum( myExpression ) > 0 , myExpression , Before( [myField] ) )