topic Aggr ONLY function in QlikView

Aggr ONLY function

Anonymous — Tue, 17 Nov 2015 03:28:25 GMT

Hi All,

I have the following 2 database tables: Insurances and Payment

1. Insurances

PersonID	InsuranceSeq	InsuranceName
1001	1	MCD
	2	SP

2. Payment

PersonID	InsuranceName	Payment($)
1001	MCD	100
	MCD	50
	SP	40
	BD	10

Desired Output:

PersonID	INS1	INS2	INS1Pay($)	INS2Pay($)
1001	MCD	SP	150	40

The only problem I am having is getting the values in last 2 columns INS1Pay($) and INS2Pay($) column. Below is what I currently have.

Dimesion: PersonID

Expression 1: ONLY({<InsuranceSeq={'1'}>} InsuranceName) //gives me MCD in 2nd column

Expression 2: ONLY({<InsuranceSeq={'2'}>} InsuranceName) //gives me SP in 3rd column

Could someone please help me get the sum value in the 3rd and 4th column? Please use column names instead of actual value like MCD since I have hundreds of different insurances. This is just a sample for 1 account (1001). I'm having hard time doing the set analysis.

Thank you.

Re: Aggr ONLY function

tresB — Tue, 17 Nov 2015 08:56:35 GMT

Sum({<InsuranceName={MCD}>} Payment($))

Sum({<InsuranceName={SP}>} Payment($))

Re: Aggr ONLY function

Anonymous — Tue, 17 Nov 2015 13:11:37 GMT

Hi Tresesco,

I cannot use specific insurance name since it's dynamic and won't remain always MCD or SP. First 2 insurance can be any from a list of 100. What I need to do is to get sum of payment based on the InsuranceSeq={'1'} and InsuranceSeq={'2'}. Any idea? Thanks.

Re: Aggr ONLY function

Mark_Little — Tue, 17 Nov 2015 13:25:09 GMT

Hi,

One approach is to make aggregated totals into in the script.

Load

InsuranceName,

SUM(Payment($)) as PaymentTotal

Resident Payment

Group by InsuranceName;

Then

ONLY({<InsuranceSeq={'1'}>} PaymentToal)

Mark

Re: Aggr ONLY function

sunny_talwar — Tue, 17 Nov 2015 13:42:12 GMT

May be this:

Script:

Table:

LOAD * Inline [

PersonID, InsuranceSeq, InsuranceName

1001, 1, MCD

1001, 2, SP

];

Join(Table)

LOAD * Inline [

PersonID, InsuranceName, Payment($)

1001, MCD, 100

1001, MCD, 50

1001, SP, 40

1001, BD, 10

];

Straight Table:

Dimension:

PersonID

Expressions:

=Only({<InsuranceSeq = {1}>}InsuranceName)

=Only({<InsuranceSeq = {2}>}InsuranceName)

=Sum({<InsuranceSeq = {1}>}[Payment($)])

=Sum({<InsuranceSeq = {2}>}[Payment($)])

Output:

Re: Aggr ONLY function

flipside — Tue, 17 Nov 2015 14:23:21 GMT

If you rename the InsuranceName in one of the tables (i.e. in the Payments table change it to InsuranceNamePayments), then you can use this, nesting your working set analysis inside another ...

sum({<InsuranceNamePayments={"$(=ONLY({<InsuranceSeq={'1'}>} InsuranceName))"}>} [Payment($)])

flipside

Re: Aggr ONLY function

Anonymous — Tue, 17 Nov 2015 16:42:28 GMT

Hi Sunny,

Your sum expressions did the trick. Thanks a lot.

Re: Aggr ONLY function

sunny_talwar — Tue, 17 Nov 2015 16:48:32 GMT

Awesome, I am glad we were able to resolve it rather quickly