topic Need Help with Set Analysis in QlikView

Need Help with Set Analysis

Mon, 08 Mar 2010 21:06:27 GMT

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

Need Help with Set Analysis

Tue, 09 Mar 2010 10:06:27 GMT

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.

Need Help with Set Analysis

Tue, 09 Mar 2010 16:35:03 GMT

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

Need Help with Set Analysis

Tue, 09 Mar 2010 17:15:41 GMT

hi Senjaya,

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

Best regards.

Need Help with Set Analysis

Tue, 09 Mar 2010 17:21:38 GMT

Miguel,

Thanks very much for the help.

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

Regards,

Senjaya

Need Help with Set Analysis

Tue, 09 Mar 2010 20:27:38 GMT

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.

Need Help with Set Analysis

Wed, 10 Mar 2010 15:46:21 GMT

Hi Miguel,

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

Regards,

Senjaya

Need Help with Set Analysis

Thu, 11 Mar 2010 11:51:24 GMT

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.

Need Help with Set Analysis

Thu, 11 Mar 2010 19:59:28 GMT

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

Need Help with Set Analysis

Thu, 11 Mar 2010 19:59:38 GMT

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

Need Help with Set Analysis

Fri, 12 Mar 2010 11:23:31 GMT

Hi Senjaya,

I've made a second load because your example was based on an "In line" table, so I couldn't make the concatenate between the two fields, you can do it in the original charge sentence.

Best regards.