topic Skipping lines with zero values in QlikView

Skipping lines with zero values

Tue, 12 Jun 2012 14:09:11 GMT

I made a table with fixed assets showing the following columns:

1. Asset number

2. AssetDescription

3. Cost,

4. Accum. Depreciation

5.Net Book Value.

Column 3 is an expression with formula (x)

Column 4 is an expression with formula (y)

Column 5 ( net book Value) is the result of column 3 -/- Column 4 i.e. Formula(x) -/- formula (y)

The user asked me to switch between Only showing columns with Net Book Value > 0 or Showing all columns ( Inlcusing NBV = 0 )

I van make a variable and let the user choose the option, but how can i implement the selection i.e. showing only columns with NVB > 0 or showing all columns ? I can see/find no option to conditionally skip a line in the table depending on a condition.

Re: Skipping lines with zero values

christophebrault — Tue, 12 Jun 2012 14:38:09 GMT

Hi,

A solution could be to create a sixth column. This one will say if NBC >0 is true or false. With this, you can use it in a list box to simply reduce your data.

write :

if(column 3 -/- Column 4)>0,'Positive NBC','NBC = 0') as NBC

Re: Skipping lines with zero values

Tue, 12 Jun 2012 15:13:56 GMT

Sorry, but i'm a bit new in QV, and dp not understand the solution.

I created a 6th column (Exprwession saying:

If ( Expression 3 -/- expression 4 =0, 'Y','N')

and indeed in the table the column is showing 'Y' if the NBV = 0, and 'N'if NBV <> 0.

But then i'm lost with your solution on how to use this in a list box to simply reduce my data?

What do i have to do with the list box then?

Re: Skipping lines with zero values

christophebrault — Wed, 13 Jun 2012 08:19:35 GMT

If your script worked well, you should have Y for the lines where NBC = 0 and N where NBC <>0.

First check if it's the case.

With this field, you can simply select only lines <> 0, =0 or both. Creat a list box with this field and you can use it.

An other solution could be to create a Button and set an action to select N in your field 6 when user clic and clear this selection when reclic. If this solution meet your needs i can explain more on How To Do.

Re: Skipping lines with zero values

Wed, 13 Jun 2012 13:59:03 GMT

I think why we do have a mis communication.

When i said i have a table, i meant i defined a table object in QV, based on 3 Data tabels/views.

1 table/view is fixed assets

1 table/view is fixed assets / years ( Per Asset/Year i have the starting balance)

1 table/view is fxied assets/Years/Periods ( per Asset/Year/Period i have the period depreciation amounts)

Based on these 3 MSSQL tables which i loaded in the QV script, i build in QV 1 'Table' object with 2 dimensions

( Asset number and asset description from table Fixed assets)

Then i have columns 3,4,5 which are all expressions like for example columns 3 Costs =

sum({$<abyYear={$(Year)},abyBalanceCode={1} >} abyAmountBegBalancePY) +

sum({$<abyYear={$(Year)},abpPeriod={">=1 <=$(Periode)"},abyBalanceCode={1} >} abpAmount)

So Column 5 showing the NBV is also an expression which has an amount as result.

The user wants to chose to show also these lines when they are 0,00 or not.

So when i did what you said, i put the expression of column 5 in a list box, but this was not working.

Re: Skipping lines with zero values

christophebrault — Thu, 14 Jun 2012 08:19:12 GMT

I though you were at script level sorry...

you can try to create a list Box using an expression :

aggr( if(Expression 3 / Expression 4 = 0,'Y','N'),Asset number)

Re: Skipping lines with zero values

Thu, 14 Jun 2012 13:08:08 GMT

Thanks a lot. This was the answer i was looking for.!