Where Clause in Script based on calculation

[#1]

Hi All,

I have a table as attached, where there are 10 clients, with Risk Level, Asset Class and Market Value.

I am trying to create a sub list of clients (in script) with the following two criteria:

1) Risk Level should be higher than 2

2) Market value of Cash should be higher than 1,000,000

It seems that Risk Level higher than 2 is relative easy to realize in where clause. However, Market value of Cash should be higher than 1,000,000 is tricky. I tried below, but does not work.

list_table:

load

ER

Resident ER_IOS

where (Risk Level > 2) and (sum{<Asset Class = {'Cash'}>}(Market Value));

Anyone could help with this?

Thanks a lot

[sinanozdemir]

Do you need to aggregate the Market Value? If not, you can just use

where (Risk Level > 2) and (Asset Class = 'Cash') and (Market Value > 1000000)

[#3]

Thanks Sinan. The problem here is that it should be Market Value for the asset class "Cash" to be > 1000000.

As there are different asset classes for each client.

Any solution?

[sinanozdemir]

Hi Kai,

I think I understood your requirements. You want to keep all other asset types that their risk levels > 2 and also asset type of Cash that market value > 1000000. If so, the below script should do:

As you can see, the first table [All Assets] has every asset type that risk level is greater 2 and asset class <> 'Cash' so we have other types of assets. In the second table Cash, I only focused on the asset class of Cash and market value > 1,000,000. The data model will look like the below:

Let me know if this is what you are looking for.

Thanks

[#5]

Thanks a lot, it helps!