Problem with creating expression

[#1]

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

[tresesco · Accepted solution]

=Count( Distinct location & place_type)

[nagaiank]

count(DISTINCT location&'|'&place_type)

[MarcoWedel]

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

[#5]

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