topic Re: Help with stright table expression in QlikView

Help with stright table expression

Thu, 26 Jun 2014 12:44:38 GMT

Hi,

Attached sample QVW describes the problem I am having and what output that I am expecting.

Could you please help me with the right expression?

Regards,

Murali

Re: Help with stright table expression

sudeepkm — Thu, 26 Jun 2014 13:15:57 GMT

Please take a look at the example attached.

Re: Help with stright table expression

Thu, 26 Jun 2014 13:20:33 GMT

Sudeep,

Thanks for your response. But I need only one row per Carrier. As in with real data each Carrier will have 100's of policies and hence I can't repeat the carrier for each policy.

Is there any other way to achieve this?

Regards,

Murali

Re: Help with stright table expression

jerem1234 — Thu, 26 Jun 2014 13:42:03 GMT

This is a weird way to show something, so it requires a little finesse. I used the formula:

=sum(aggr(pick(match(PolicyRef,$(=concat(distinct chr(39) & PolicyRef & chr(39), ','))), $(=concat(distinct 'sum({<PolicyRef = {' & chr(39) & PolicyRef & chr(39) & '}>} total Premium)',','))), Carrier, PolicyRef))

However, if you know what PolicyRef's there are and know they won't change you may hardcode them like:

=sum(aggr(pick(match(PolicyRef, 'Pol A', 'Pol B', 'Pol C'), sum({<PolicyRef = {'Pol A'}>} total Premium), sum({<PolicyRef = {'Pol B'}>} total Premium), sum({<PolicyRef = {'Pol C'}>} total Premium)), Carrier, PolicyRef))

Please find attached.

Hope this helps!

Re: Re: Help with stright table expression

sudeepkm — Thu, 26 Jun 2014 13:49:05 GMT

Please find the attachment.

Re: Help with stright table expression

eduardo_sommer — Thu, 26 Jun 2014 14:10:09 GMT

Hi,

Use Carrier as the dimension and the following expression:

=sum(aggr(sum(total<PolicyRef> Premium),Carrier,PolicyRef))

Eduardo