topic Re: Problem with creating expression in QlikView

Problem with creating expression

Wed, 04 Jun 2014 10:21:16 GMT

Hi, I have a problem with creating correct expression to my case : I have a table like this :

location	place_type
A	1
A	1
B	1
C	2
D	1
D	2
E	1

I need to calculate how many different location do i have like tihs :
row1 - A 1 , and row2 - A 2 is counted as 1 because location and place_type are the same

then in row5 and row6 i have D 1 and D 2 which are different so i have to count this as a 2
In this example the result should be 6

I tried do some Count(Distinct... but it ignore the fact that row1 and row2 are the same and count it as a 2 .

Im beginner in QlikView so any help would be amazing

Re: Problem with creating expression

tresB — Wed, 04 Jun 2014 10:29:16 GMT

=Count( Distinct location & place_type)

Re: Problem with creating expression

nagaiank — Wed, 04 Jun 2014 10:30:49 GMT

count(DISTINCT location&'|'&place_type)

Re: Problem with creating expression

MarcoWedel — Wed, 04 Jun 2014 10:52:37 GMT

or at script level try adding

AutoNumberHash128(location, place_type) as LocationPlaceTypeID

in your LOAD statement to create a distinct ID for each combination of location and place_type.

In the visualization you then would use an expression like

Count(Distinct LocationPlaceTypeID)

Depending on the size of your tables this might also improve your application's performance because the string calculations are done during load time.

hope this helps

regards

Marco

Re: Problem with creating expression

Wed, 04 Jun 2014 12:04:59 GMT

Hi Jakub,

Create a dummy field (key field) of combination of location & place_type.

and take distinct count of that new field.

In script -
location&place_type as key

in front end -

count(distinct key)

you will get the result.

Lalit