topic Loading from 2 QVDs then exclud some data in QlikView

Loading from 2 QVDs then exclud some data

Wed, 28 Mar 2012 15:49:54 GMT

Hi,

I am loading from two different QVDs.

The first QVD has the calls data such as Number called, duration.. and the second QVD has the Employee information such as Names, Departments..

I have excluded some of the departments as I need to do.

While building a chart to show the total Nr called by Department I have on of the columns in the chart has no Department name.

i know the reason is that I have excluded some of the departments..

How can I hide this column from the chart?

Thanks for your help

Loading from 2 QVDs then exclud some data

Wed, 28 Mar 2012 15:51:06 GMT

Under Dimensions select Suppress Null Values

Loading from 2 QVDs then exclud some data

Wed, 28 Mar 2012 15:52:49 GMT

That will hide it but the total on the chart doesn't change..it still shows me the total including the hidden departments

Loading from 2 QVDs then exclud some data

Wed, 28 Mar 2012 15:54:56 GMT

You can us set analysis to exclude them also:

Count({<Department-={'A','B','C','D','E','F'}>} NrCalled)

Re: Loading from 2 QVDs then exclud some data

Wed, 28 Mar 2012 16:02:58 GMT

Do you mean I have to enter the Department names instead of A, B, C..?

I am trying to avoid hard coding department names..

Any other solutions?

Also I tried to see how it works with one department and did: =NUM(Count({<Department-={'DirectSales - Drury'}>} FinalParty),'#,##0')

It gave me a wrong Nr!

Thanks:)

Re: Loading from 2 QVDs then exclud some data

Wed, 28 Mar 2012 16:10:18 GMT

In fact this work fine..

But is there anyway where I don't need to hard code the department names.

Thxs

Re: Loading from 2 QVDs then exclud some data

Wed, 28 Mar 2012 16:13:42 GMT

Do the departments that you want to exclude have a certian criteria or flag assigned to them?

Re: Loading from 2 QVDs then exclud some data

Wed, 28 Mar 2012 16:15:07 GMT

Nope! that would be helpfull though

Re: Loading from 2 QVDs then exclud some data

Wed, 28 Mar 2012 16:16:29 GMT

I guess another way would be to change how the QVD files are joined in the script.

Loading from 2 QVDs then exclud some data

Wed, 28 Mar 2012 19:02:15 GMT

This sound great..do you know how to use set analysis in the expression edit..

Like I have a drill down chart and the first dimention is month..

I am trying to do the same thing

I tried:

= Only({<Department-={'DirectSales - Drury'}>} Month),'#,##0')

It didn't work!

Thxs

Loading from 2 QVDs then exclud some data

Jason_Michaelides — Thu, 29 Mar 2012 12:22:24 GMT

There is a way to dynamically put all the departments in a set analysis expression, which I've outlined below. However, it does sound to me like you'd be best to join the department and calls tables together in the script and then use the "suppress null values" as previously suggested. Or flag null departments in the script and use a simple set analysis expression Count({<Flag={Yes}>} NrCalled)

It's almost always best to do the calculations in the script if they don't depend on user selections as it reduces the load on the server in use. Of course the balance is potentially extended load times so you do need to evaluate on a case by case basis.

Anyway, to dynamically put all departments in the set analysis expression you need to build the expression in a variable and then evaluate the variable in the expression. So create a variable vNrCalls:

='Count({<Department={' & Concat(Distinct Chr(39)&Department&Chr(39),',') & '}>} NrCalled)'

Then in the chart expression just put in $(vNrCalls) which will evaluate to

Count({<Department={'A','B','H','J'}>} NrCalled) with whatever departments are available in the current selections. If you always want all departments then obviously adjust the set analysis with the appropriate {1}

Hope this helps,

Jason

Loading from 2 QVDs then exclud some data

Jason_Michaelides — Thu, 29 Mar 2012 12:25:10 GMT

Oops! Just realised that either the webpage or my iPhone has replaced & with &amp...you'll need to adjust!

Loading from 2 QVDs then exclud some data

Jason_Michaelides — Thu, 29 Mar 2012 12:25:58 GMT

Oh I give up - you get the idea!!