Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Creating a query (calculated field) to use as a selection criteria

I am loading in my data in the following format;

Project ID     Year       Total Cost     Total Benefit

1                    14          £10,000        £5,000

1                    15          £5,000          £0,000

1                    16          £0                 £1,000

2                    14          £7,000          £2,000

2                    15          £6,000          £1,000

3                    15          £1,000          £0

I would like to filter my data for projects that deliver a benefit of more than £0.  When I simply put an IF statement into my load script, it evaluates each piece of data getting loaded in.  That means that red line above gets tagged with false... yet the other two lines (in green) of financials for Project ID = 1, get a true.

What I really want it to do, is evaulate the project as a whole, and show that Project ID 1 does have a benefit, and therefore when I make my filter on my dashboard, it gives me all the data for that project.

If it were a Access Database, I guess I would be looking to create a query, with a calulated field, in which I could then use to apply my filter on.  I have no idea how to achieve this in Qlikview though.


Any help would be much appreciated.


Many thanks in advance

Tags (2)
2 Replies

Re: Creating a query (calculated field) to use as a selection criteria

If the table containing your values is for example Tab1 then:

Load * resident Tab1;

left join

Load [Project ID], Sum(Total Benefit) as Prj_Tot resident Tab1 Group by [Project ID];

Now all the rows containing a value for Prj_Tot are the rows you need ...

Highlighted
Employee
Employee

Re: Creating a query (calculated field) to use as a selection criteria

Hi Simon,

I would recommend you perform this filter in the UI and not at the time of load the data.  This will avoid limiting the analysis you can do on the data.  You may want to consider creating a specific button to make the selection as desribed in this posting:  Using Expressions to Make Selections

You also cloud do it in he expression in the chart using an expression in the dimension such as as If (Sum([Total Benefit]) > 0, [Project ID])

-Josh

Qlik

Community Browser