Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Skipping lines with zero values

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.

1 Solution

Accepted Solutions
christophebrault
Specialist
Specialist

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)

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin

View solution in original post

6 Replies
christophebrault
Specialist
Specialist

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

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Not applicable
Author

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?

christophebrault
Specialist
Specialist

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.

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Not applicable
Author

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.

christophebrault
Specialist
Specialist

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)

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Not applicable
Author

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