Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Using a column within a table to exclude specific rows

Hi

I'm fairly new to QlikView and I'm trying to teach myself the basics before I undertake any advance training. So any help would be very much appreciated and please excuse the simplicity of my question.

I have a products table (linked to a projects table) which has two columns that are used to identify whether the product(service) has been completed, and if completed, whether it has been invoiced for. With an additional column which is a 'deleteflag' column, which deletes the file from the front end system but not the database. I would like to take these fields into account when putting together a list box, for example:

For every project number there's an x amount of products attached to it. So for each project, I would like a column in my list box that identifies the total value for all uncompleted products, completed products, and a column of those products that have been completed, the value of those that have been invoiced.

I can do simple expressions, (sum and average), and I found that you can use the SET NullValues so that QlikView takes the Nulls into account (for example the Completed column would included Nulls or 0 for any product that hasn't been completed).

Thank you

3 Replies
Not applicable

Re: Using a column within a table to exclude specific rows

I knew it was simple and with some searching I did eventually get there.

To get the total value of completed products, I used this:

sum(if(COMPLETED='1',TOTALVALUE))

How could I change this to show the value of uncompleted products? Completed = '0' & 'Null'?

I have used the SET NullValue to '<Null>' in my script

Not applicable

Re: Using a column within a table to exclude specific rows

Are you using an expression in the COMPLETED column? Or is COMPLETED a field in your data model?

Not applicable

Re: Using a column within a table to exclude specific rows

COMPLETED is a field in my data model

But I also have a DELETEFLAG field in my data model for each of my tables which also has the values of 1 or 0/Null, which is my next step in validating my data

Community Browser