topic Re: How to combine values from the same column into one condition in QlikView

How to combine values from the same column into one condition

Tue, 07 Jun 2016 17:50:14 GMT

Hello all,

I've been working in QlikView for a while now. I have a question about a certain column I've been working with. I have a table called 'Jobs' that counts all the jobs my company's won for our client. One of the columns is Status. Status has three possible values: Lost, Won, and Inactive.

I've been asked to create a bar chart that measures Won jobs vs the other two possible values (Lost and Inactive) by Salesperson. Can anyone help me with an expression I can use for this? I need to somehow combine Lost and Inactive Jobs into one bar.

The expression would be something like

NumericCount(Status, if(Status === 'Lost' OR Status === 'Inactive), 'Other Jobs', 'Won Jobs')

but it doesn't seem to be working.

Re: How to combine values from the same column into one condition

sunny_talwar — Tue, 07 Jun 2016 18:01:25 GMT

You can try using a calculated dimension:

If(Status = 'Won', 'Won Jobs', 'Other Jobs')

Re: How to combine values from the same column into one condition

Tue, 07 Jun 2016 18:10:56 GMT

Thanks Sunny. But how can I implement this if I wanted to count the number of jobs?

I tried TextCount(If(Status = 'Won', 'Won Jobs', 'Other Jobs')), and this didn't work. Any pointers for modifying it to work with TextCount?

Re: How to combine values from the same column into one condition

sunny_talwar — Tue, 07 Jun 2016 18:40:28 GMT

Just use Count(Status) as your expression and If(Status = 'Won', 'Won Jobs', 'Other Jobs') as your dimension

Re: How to combine values from the same column into one condition

Qrishna — Tue, 07 Jun 2016 19:02:49 GMT

This way?

Re: How to combine values from the same column into one condition

Tue, 07 Jun 2016 19:38:02 GMT

Yes, just like this! But how did you get this chart to pop up? What was the expression that you used?

Re: How to combine values from the same column into one condition

Qrishna — Tue, 07 Jun 2016 19:46:00 GMT

Please see attached. !

Expressions Used:

=Aggr(Count({$<Status={'Won'}>}Job),SalesPerson) - 'Won ' Jobs Count

=Aggr(Count({$<Status={'*'}-{'Won'}>}Job),SalesPerson) - 'Rest' Jobs Count

Re: How to combine values from the same column into one condition

MindaugasBacius — Tue, 07 Jun 2016 20:05:18 GMT

I prefer stalwar1 solution as simple to understand.

Open Bar chart properties. Right click on the chart > Properties.

1. Just in Calculated dimension write the expression:

If(Status = 'Won', 'Won Jobs', 'Other Jobs')

2. Add in expression field this statement:

Count(Status)

Re: How to combine values from the same column into one condition

Qrishna — Tue, 07 Jun 2016 20:13:23 GMT

AFAIK,calculated dimensions usually have a big impact on performance (especially with huge data). use of set analysis for restrictions over IF() stements is recommended ,especially in charts .

Re: How to combine values from the same column into one condition

sunny_talwar — Tue, 07 Jun 2016 20:17:33 GMT

That's true, then I would suggest creating a field in the script only.

LOAD Status,

If(Status = 'Won', 'Won', 'Rest') as New_Status

Resident Table;

and then use New_Status as Dimension and Count(New_Status) as your expression.

Alternatively, I don't understand the usage of Aggr() in your solution. Why not just use these?

=Count({$<Status={'Won'}>}Job)

=Count({$<Status={'*'}-{'Won'}>}Job)

Re: How to combine values from the same column into one condition

sunny_talwar — Tue, 07 Jun 2016 20:20:51 GMT

Here is the solution where script modifications are made:

Jobs:

LOAD *,

If(Status = 'Won', 'Won', 'Rest') as New_Status;

load * inline [

Job,SalesPerson,Year,Status

1,AA,2014,Won

2,AA,2014,Lost

3,BB,2014,Won

4,CC,2014,Inactive

5,CC,2015,Won

6,AA,2015,Lost

7,CC,2015,Won

8,BB,2016,Inactive

];

Re: How to combine values from the same column into one condition

Qrishna — Tue, 07 Jun 2016 20:26:56 GMT

Yes this is the method(doing most part at the script level) that i would also prefer.

using Aggr() - i am grouping with respect to 'Sales person' else count funct returns other values depending on the selections made.