Need Help with Set Analysis

[#1]

Dear All,

I have 2 tables : 1 is the ciity table contains City, State_ID , Population, GRP on City Level

2 is the State table.

Then the final pivot table I have to make is shown as :

State, GRP on State level,

To Calculate the GRP on State Level - I have to use the set analysis which I am no master of.

GRP on State Level= (GRP on City Level * Population on City level) / Total Population on State Level)

Total Population on State Level= Total Population on City 1+2+... for every citry belong in that state.

Any help will be appreciated.

Thanks

[#2]

Hi Senjaya,

I don't know why you are trying to do it using set analysis. Here you are a possible solution using aggr function.

I hope this helps you, if you have any doubts, please, let me know it.

Best regards.

[#3]

Hi Miguel,

I really hope that I can do it with the agregate function - but the client want it in pivot table

in this format : State Name, GRP in state level

As you know I have to do calculation on the fly for GRP in the state level

[#4]

hi Senjaya,

Could you modify my example? this will help me to understand what you want.

Best regards.

[#5]

Miguel,

Thanks very much for the help.

I thought - I misread the calculation. Your file really help me a lot.

Regards,

Senjaya

[#6]

I have the different year for the city population -- that what thru me off . And there is dimension called "Broadcast Year" which I have to link it to this year.

I did not rename the column the same in the qlikview- because it create unneccessary loop.

That's the whole reason I stuck with it.

[#7]

Hi Miguel,

I upload your file with the twist to my existing rpoblem- hope you can take a look

Regards,

Senjaya

[#8 · Accepted solution]

Hi Senjaya,

I've made a solution using the both fields from both tables (Year-City) vs (Brct_Year-City) if you have a problem with the Brct_Year field this is a possible way.

I hope this helps you. Let me know if this solution isn't right.

Best regards.

[#9]

Thanks Miguel - this is good.

The only small problem is :

FinalT1:
Load
Brct_Year,
City as CityT1,
Brct_Year & City as KeyT1,
State,
[GRP on City Level]
resident Table1;

The table : Table1 is so large and contains 450 millions records. just the second load alone will cause me double the load. To load the table1 it took almost 5 hours.

Regards,

Senjaya

[#10]

Thanks Miguel - this is good.

The only small problem is :

FinalT1:
Load
Brct_Year,
City as CityT1,
Brct_Year & City as KeyT1,
State,
[GRP on City Level]
resident Table1;

The table : Table1 is so large and contains 450 millions records. just the second load alone will cause me double the load. To load the table1 it took almost 5 hours.

Regards,

Senjaya