Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Need Help with Set Analysis

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Need Help with Set Analysis

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.

10 Replies
Not applicable

Need Help with Set Analysis

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.

Not applicable

Need Help with Set Analysis

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

Not applicable

Need Help with Set Analysis

hi Senjaya,

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

Best regards.

Not applicable

Need Help with Set Analysis

Miguel,

Thanks very much for the help.

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

Regards,

Senjaya

Not applicable

Need Help with Set Analysis

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.

Not applicable

Need Help with Set Analysis

Hi Miguel,

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

Regards,

Senjaya

Not applicable

Need Help with Set Analysis

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.

Not applicable

Need Help with Set Analysis

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

Not applicable

Need Help with Set Analysis

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

Community Browser