topic Re: URGENT: show zeros after data reduction in QlikView

URGENT: show zeros after data reduction

Wed, 13 Nov 2013 16:59:50 GMT

Hi,

I have a QV file which displays a list of various calulated values in a straight table i.e. the headers would be

ID	description	sum(value)
1	This is all countries with EUR	12546

I have implemented a security model that performs the data reduction based on NTNAME eg.

load * inline [

ACCESS, NTNAME, CURRENCY

ADMIN,JOHNSMITH,

USER, WHSMITH, EUR

];

When I am in as ADMIN I can see a complete list of rows, even the ones that are zero. This is because I have deselected the "Suppress zero-values" option in the presentaion tab.

My problem is: when I am in as USER, although the data gets filtered out for to only show EUR, QV removes all the rows from the straight table that have zero in the sum(value) column. NOTE: "Suppress zero values" is still deselected.

I need to show these zero values when I am in as USER. Can someone please let me know how to do this?

Thanks,

Re: URGENT: show zeros after data reduction

christian77 — Wed, 13 Nov 2013 17:11:22 GMT

Hi.

Try checking show all values in the dimension tab.

Keep unchecked supress zero values.

Re: URGENT: show zeros after data reduction

christian77 — Wed, 13 Nov 2013 17:13:20 GMT

Also, you probably have to review your section access.

Re: URGENT: show zeros after data reduction

israrkhan — Wed, 13 Nov 2013 17:20:25 GMT

Hi,

i am not sure exactly, but i think qlikview automatically hides 0 values in section access.

i would give a try to change 0 values to 1, just for checking purpose, in script,like if (field= 0,1, field).

give a try and see, whether its a default behavior or ???

Re: URGENT: show zeros after data reduction

Anonymous — Wed, 13 Nov 2013 17:50:50 GMT

Maybe section access removes all data from your application.

Can you upload your file?

Re: URGENT: show zeros after data reduction

Wed, 13 Nov 2013 17:59:26 GMT

Are checked the Strict Exlucsion Property on the document properties?

Re: URGENT: show zeros after data reduction

Wed, 13 Nov 2013 18:40:11 GMT

Hi all,

Thanks for all your contributions.

I have inspected the issue more closely and this is what I find.

The way I load values into the straight table is:

table_description:

load * inline[

ID, ID_linked, description

1,1,This is all countries with EUR

2,2,This is all countries with GBP

];

table_calculation:

load

'1' as ID_linked,

datakey,

sum(currency) as value

resident master_table

where country = 'EUROPE'

group by datakey;

concatenate load

'2' as ID_linked,

datakey,

sum(currency) as value

resident master_table

where country = 'UK'

group by datakey;

To create my straight table I add the fields [ID] and [description] to the dimensions and sum the [value] field in the expression. I then select "Show all values" for [ID] and deselect "Suppress zero values". The reason I use [ID] is, if there is a [ID_linked] for which no records are found, the value of the [ID_linked] will not be in QV, but by using [ID] I can force show it.

This works perfectly when there is no data reduction.

When there is data reduction, any [ID] which did not have a [ID_LINKED] appears to be taken out completely from the file. I.E. any forced zero values disappear.

Is there a method/tick box etc. in qlikview that stops this from happening?

Appreciate this is alot of stuff,so let me know if you have further questions.

Unfortunately i cannot send you my file.

thanks

Re: URGENT: show zeros after data reduction

Wed, 13 Nov 2013 18:42:08 GMT

hi dathu,

i currently have that as unchecked and it does not work

Re: URGENT: show zeros after data reduction

Anonymous — Wed, 13 Nov 2013 18:50:41 GMT

What looks wrong is that there is no CURRENCY field in the part of the script you just sent (I assume it is section application).

For data reduction to work properly, you must have the reduction field (in your case CURRENCY) in both section access and section application. Additionally, the field name and the data values must be all caps.

Not much can add without the file.

Re: URGENT: show zeros after data reduction

Thu, 14 Nov 2013 12:46:59 GMT

thanks all for the input.

I now understand the root cause and this problem cannot be solved through QV.

thanks for the help