Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Pass value from an Island table to chart expression

Hi!

I have a lookup table which I don't want to join with any of source table due to changing requirements. It contains values that match (not in full) calculated dimensions in some charts. I need to create expressions in those charts that use values from a lookup corresponding to values in dimension(s). Could you please help with this?


Example:

Lookup:

AGE_18GROUPCountryPOPULATIONSTANDARD_CODE
0 - 4 yearsUS107258150
0 - 4 yearsUS113320160
0 - 4 yearsUS18986520204
5 - 9 yearsUS87591150
5 - 9 yearsUS104235160
5 - 9 yearsUS19919840204
10 - 14 yearsUS73785150
10 - 14 yearsUS93538160
10 - 14 yearsUS20056779204
15 - 19 yearsUS70450150
15 - 19 yearsUS73717160
15 - 19 yearsUS19819518204



Chart (aggregated data):

AGE_18GROUPCount of IDsPOPULATIONRatio
0 - 4 years100
5 - 9 years200
10 - 14 years300
15 - 19 years400


How to get Lookup.POPULATION values corresponding to  Chart.AGE_18GROUP and Lookup.STANDARD_CODE=204 for Chart.POPULATION and use it further to get Ratio (Count of IDs/POPULATION)?


I really appreciate your help!


Thank you!

Tags (3)
8 Replies
MVP
MVP

Re: Pass value from an Island table to chart expression

Can you give what could be the result as per your requirement?

Not applicable

Re: Pass value from an Island table to chart expression

It should be like this:

AGE_18GROUPCount of IDsPOPULATIONRatio
0 - 4 years100189865200.000005
5 - 9 years200199198400.000010
10 - 14 years300200567790.000015
15 - 19 years400198195180.000020
MVP
MVP

Re: Pass value from an Island table to chart expression

Try This....

COUNT(ID)/SUM({<STANDARD_CODE = {204}>}POPULATION)

Not applicable

Re: Pass value from an Island table to chart expression

Manish,

It doesn't work.

Not applicable

Re: Pass value from an Island table to chart expression

Hi Elena,

Couple of questions: are the tables linked on AGE_18GROUP or is it really an island table, i.e. no link with the rest of the data set? What is the ratio a quotient of?

If possible, please post a sample as well.

Best,

Matt

Not applicable

Re: Re: Pass value from an Island table to chart expression

Hi Matt,

Yes, tables are islands.

Please see attached. I need to write a correct expressions for TOTAL and RATIO in a chart 'RESULT'.

Data is just for illustration purposes. Important thing - we should avoid join or mapping in script load. Picking up appropriate population value should be done on a report level.

Thank you!

Elena

Not applicable

Re: Re: Re: Pass value from an Island table to chart expression

Hi,

This should work:

sum(if(AGE_18GROUP = AGE_GROUP AND STANDARD_CODE = 204, POPULATION))


See attached. My figures aren't matching exactly what you have above though since there are multiple ID fields per standard code. Any way we can limit the expression based on the ID field to get what you want?


Matt 

Not applicable

Re: Re: Pass value from an Island table to chart expression

Matt,

That formula produces wrong result.

I found the solution. I have to use AGGR on both fields AGE_GROUP and AGE_18GROUP while calculating maximum of POPULATION per corresponding age group and standard code =>

=aggr(max(if(AGE_GROUP=AGE_18GROUP and STANDARD_CODE = '204',POPULATION)), AGE_GROUP, AGE_18GROUP)