topic Re: Remove "0" from Empty Line in QlikView

Remove "0" from Empty Line

bwisealiahmad — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a table that has some IF statements I am trying to remove because it loads quite slow sometimes. On lines that are emtpy a "0" comes up and I am trying to remove this. Right now the expressions is:

IF(

Report = Test

IF($(ReportName)_blankrow = 1, ' ', Sum(Expression))

Which produces something like this:

Now if I remove the _blankrow check part it looks like this.

Does anyone have a good suggestion for making it look like the first without using an IF statement in the front end?

Thanks,

Ali

Re: Remove "0" from Empty Line

sunny_talwar — Mon, 23 Jan 2017 15:12:19 GMT

You can try with Pick() May be....

Pick($(ReportName)_blankrow, '', Sum(Expression))

Assuming the flag for places which have value is 2...

Re: Remove "0" from Empty Line

bwisealiahmad — Mon, 23 Jan 2017 15:17:30 GMT

It is either "1" or " "

in the listbox. I've been thinking of trying with "Replace" in the script somewhere.

Re: Remove "0" from Empty Line

krishnacbe — Mon, 23 Jan 2017 15:18:23 GMT

Try using Set expression

Sum({<$(ReportName)_blankrow={"1"}>}Expression)

Re: Remove "0" from Empty Line

sunny_talwar — Mon, 23 Jan 2017 15:19:33 GMT

Then how about this:

Pick(RangeSum($(ReportName)_blankrow, 1), Sum(Expression), ' ')

Re: Remove "0" from Empty Line

bwisealiahmad — Mon, 23 Jan 2017 15:41:53 GMT

Pick Match vs Nested If Performance

I am thinking something like this. Maybe a Pick Match combination?

Re: Remove "0" from Empty Line

sunny_talwar — Mon, 23 Jan 2017 15:43:36 GMT

The above expression did not work?

Re: Remove "0" from Empty Line

bwisealiahmad — Mon, 23 Jan 2017 15:47:03 GMT

Nope:

If(Report<>'Hours',

Pick(RangeSum($(Rapport)_blankrow, 1), Sum({$<$(Rapport)_Traff = {1} >}[Resultat Belopp])/$(B_enhet) , '')

,

Pick(RangeSum($(Rapport)_blankrow, 1), Sum({$<$(Rapport)_Traff = {1},_Valutaflagga={'0'} >}Quantity_Act) , '')

)

Re: Remove "0" from Empty Line

sunny_talwar — Mon, 23 Jan 2017 16:00:20 GMT

Can you add this as an expression and see what you get?

RangeSum($(Rapport)_blankrow, 1)

Ideally it should show 2 for places you want to see '' and 1 for places where you want to see the sum....

Re: Remove "0" from Empty Line

bwisealiahmad — Mon, 23 Jan 2017 16:05:21 GMT

I get a "1" in every single cell in the column.

Re: Remove "0" from Empty Line

sunny_talwar — Mon, 23 Jan 2017 16:12:50 GMT

Would you be able to share a sample or may be images of what you are seeing?

Re: Remove "0" from Empty Line

bwisealiahmad — Mon, 23 Jan 2017 16:18:02 GMT

See attached

Re: Remove "0" from Empty Line

sunny_talwar — Mon, 23 Jan 2017 16:21:25 GMT

It's strange that it worked here

I mean $(ReportName)_blankrow, but not in my expression... I see you used vRapport... what is the difference here?

Re: Remove "0" from Empty Line

bwisealiahmad — Mon, 23 Jan 2017 16:24:28 GMT

I just used a different name for the variable because I translate the scripts usually to English.

Here is the original:

If(Report<>'Hours',

if($(Rapport)_blankrad=1 ,'', Sum({$<$(Rapport)_Traff = {1} >}[Resultat Belopp])/$(B_enhet) )
,
if($(Rapport)_blankrad=1 ,'', Sum({$<$(Rapport)_Traff = {1},_Valutaflagga={'0'} >}Quantity_Act) )
)

Re: Remove "0" from Empty Line

sunny_talwar — Mon, 23 Jan 2017 16:30:24 GMT

If the above is working, this should work too

If(Report<>'Hours',

Pick(RangeSum($(Rapport)_blankrad, 1), Sum({$<$(Rapport)_Traff = {1} >}[Resultat Belopp])/$(B_enhet), ''),

Pick(RangeSum($(Rapport)_blankrad, 1), Sum({$<$(Rapport)_Traff = {1},_Valutaflagga={'0'} >}Quantity_Act), ''))

Re: Remove "0" from Empty Line

bwisealiahmad — Mon, 23 Jan 2017 16:51:49 GMT

That worked! Now just implement and test Thanks like always!

Re: Remove "0" from Empty Line

sunny_talwar — Mon, 23 Jan 2017 16:54:50 GMT

Awesome, but do you see any performance improvements?

Re: Remove "0" from Empty Line

bwisealiahmad — Mon, 23 Jan 2017 16:56:56 GMT

Took out memory statistics before and will change all expressions in object and check afterwards. Can post results when I have changed all the expressions.

Re: Remove "0" from Empty Line

bwisealiahmad — Mon, 23 Jan 2017 17:24:29 GMT

Changing between reports type is going faster now, but there is still performance issue when changing year month etc. There is al link table where YearMonthAccountNr is a key in the general ledger and in the link table and from there Date is linked to the Calendar Table.

Not very intuitive Think it is causing it to perform worse.

Re: Remove "0" from Empty Line

sunny_talwar — Mon, 23 Jan 2017 17:29:40 GMT

May be get rid of the first if statement also....

Pick(RangeSum($(Rapport)_blankrad, 1), RangeSum(

Sum({$<$(Rapport)_Traff = {1}, Report -= {'Hours'}>}[Resultat Belopp])/$(B_enhet),

Sum({$<$(Rapport)_Traff = {1},_Valutaflagga={'0'}, Report *= {'Hours'}>}Quantity_Act)), '')