Aggr ONLY function

[Anonymous]

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.

[tresesco]

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

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

[Anonymous]

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.

[Mark_Little]

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

[sunny_talwar · Accepted solution]

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:

[flipside]

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

[Anonymous]

Hi Sunny,

Your sum expressions did the trick. Thanks a lot.

[sunny_talwar]

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