topic Tricky Set Analysis in QlikView

Tricky Set Analysis

Tue, 24 Nov 2009 05:05:02 GMT

This is the script:

Person:
load * inline [
PersonId, PersonName, Country, PersonCount
1, 111, US, 1
2, 222, GB, 1
1, 111, US, 2
3, 333, RU, 1
4, 444, US, 1
5, 555, TW, 1
6, 666, CH, 1
7, 777, CH, 1
];

Countries:
Load * inline [
Country
US
GB
RU
TW
CH
];

CountryCounts:
Load * inline [
Country, CountryPower
US,1
GB,2
RU,3
TW,4
CH,5
];

I need to create a chart table like:

PersonId, PersonName, Country as dimentions

And as a fact - =sum(PersonCount*CountryPower)

I did this - =sum({$ <Country={$(=only(Country))} >}CountryPower * PersonCount)

It will work fine if you select one country only. But it doesn't for all.

Is there any idea how to work out the task?

Thanks

Tricky Set Analysis

Tue, 24 Nov 2009 15:00:09 GMT

why don'y you use just the exressions:

=sum(PersonCount*CountryPower)

Tricky Set Analysis

Tue, 24 Nov 2009 15:07:19 GMT

Not exactly sure what you are looking for, but I think the <aggr> could help you here because it can use the dimension as a parameter.

aggr ( [distinct | nodistinct ] [set_expression] expression {, dimension} )

Using your data it would look like:

Column

A: =sum(PersonCount*CountryPower)

B: aggr(Sum(CountryPower*PersonCount),Country)

C: aggr(NODISTINCT Sum(CountryPower*PersonCount),Country)

PersonId	PersonName	Country	A	B	C
			23	-	-
1	111	US	3	4	4
2	222	GB	2	2	2
3	333	RU	3	3	3
4	444	US	1	-	4
5	555	TW	4	4	4
6	666	CH	5	10	10
7	777	CH	5	-	10

Tricky Set Analysis

Tue, 24 Nov 2009 23:05:53 GMT

So the problem itself is not such simple. I've already found the solution for the task. But anyway thank you all for your help. I appreciate it.

Thanks