How to hide / remove rows in pivot chart

[#1]

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

[alexandros17]

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

[#3]

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

[alexandros17]

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"

[#5]

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.

[#6]

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

[#7]

Hi,

Both tables contains duplicate records.

If i do left outer join its making duplicate counts.

--Prabhu

[#8]

Try with straight table with hide & show columns

[erivera10]

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?