topic Re: How to hide / remove rows in pivot chart in QlikView

How to hide / remove rows in pivot chart

Tue, 04 Mar 2014 10:48:01 GMT

Please find Sample tables below.

TempA:

LOAD * Inline [

j_col, data1, data2, total

1, 10, 15, 25

2, 15, 20, 35

3, 20, 25, 45 ];

TempB:

LOAD * Inline [

j_col, data1, data2, total

1, 10, 15, 25

2, 15, 10, 25 ];

I want to show above data in pivot chart based on j_col, for only mismatched counts of other columns

here in this case to show only records for "j_col" value is 2,3.

To Show	j_col	data1	data1	Difference	data2	data2	difference
No	1	10	10	0	15	15	0
Yes	2	15	15	0	20	10	10
Yes	3	20	0	20	25	0	25

Thanks in advance

--Prabhu

Re: How to hide / remove rows in pivot chart

alexandros17 — Tue, 04 Mar 2014 10:52:28 GMT

j_col is not a doimension so the only way to hide rows is having values for alll columns equal to zero (and check the box exclude zero values)

To do this test each column (expression) like this: if(column(1) > 1, expression, 0)

Hope it helps

Re: How to hide / remove rows in pivot chart

Tue, 04 Mar 2014 10:57:44 GMT

Thanks for quick replay Saccone.

In my Pivot chart, consider j_col as a dimension.

apart from j_col, i have 3 more dimension columns.

--Prabhu

Re: How to hide / remove rows in pivot chart

alexandros17 — Tue, 04 Mar 2014 11:02:21 GMT

So it is easier,

write (modify) j_col dimension in this way:

If(j_col>1, j_col, null())

and check the box "exclude null values"

Re: How to hide / remove rows in pivot chart

Tue, 04 Mar 2014 11:23:36 GMT

Hi Narsimha,

You can also do a join between both tables and use flags to use in the expressions. Remember Qlikview link tables thougth same name fields and in your tables, all fields are called equal.

Yo can do something like this:

TempA:

LOAD * Inline [

j_col, data1, data2, total

1, 10, 15, 25

2, 15, 20, 35

3, 20, 25, 45 ];

left join (TempA)

//TempB:

LOAD * Inline [

j_col, TMP_B.data1, TMP_B.data2, TMP_B.total

1, 10, 15, 25

2, 15, 10, 25 ];

Then you can load again the TempA table in script and with all fields now loaded in the same structure, you can create new fields for differences for example, and here you can add flags for when difference is 0 for example.

Then in your dashboards charts, you can filter the rows by your flag and exclude some rows.

Regards.

Re: How to hide / remove rows in pivot chart

Tue, 04 Mar 2014 11:47:32 GMT

Thanks for your solution.

The Above Solution is working only for both tables contains Unique j_col values.

If both Tables contains duplicates then matched counts records also showing.

I am sending again Data with Duplicates.

Please check this

TempA:

LOAD * Inline [

j_col, data1, data2, total

1, 10, 15, 25

1, 30, 15, 45

2, 15, 20, 35

2, 50, 30, 80

3, 20, 25, 45 ];

TempB:

LOAD * Inline [

j_col, data1, data2, total

1, 10, 15, 25

1, 30, 15, 45

2, 15, 10, 25

2, 15, 1, 16

3, 5, 1, 6];

--Prabhu

Re: How to hide / remove rows in pivot chart

Tue, 04 Mar 2014 12:57:36 GMT

Hi,

Both tables contains duplicate records.

If i do left outer join its making duplicate counts.

--Prabhu

Re: How to hide / remove rows in pivot chart

Tue, 04 Mar 2014 13:50:44 GMT

Try with straight table with hide & show columns

Re: How to hide / remove rows in pivot chart

erivera10 — Wed, 09 Jul 2014 14:51:38 GMT

Alessandro Saccone

Hello such:

Your condition says that if j_col is greater than 1, put j_col, if not put null and notice that I have a similar case, but with condition of dates, a range, but if it is the same principle in a pivot table, but instead of null I need to remove them. You know how you could make?