Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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"
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.
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
Hi,
Both tables contains duplicate records.
If i do left outer join its making duplicate counts.
--Prabhu
Try with straight table with hide & show columns
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?