topic Re: Where Clause in Script based on calculation in QlikView

Where Clause in Script based on calculation

Mon, 06 Jul 2015 20:58:28 GMT

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

Resident ER_IOS

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

Anyone could help with this?

Thanks a lot

Re: Where Clause in Script based on calculation

sinanozdemir — Mon, 06 Jul 2015 21:25:07 GMT

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)

Re: Where Clause in Script based on calculation

Tue, 07 Jul 2015 08:00:15 GMT

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?

Re: Where Clause in Script based on calculation

sinanozdemir — Tue, 07 Jul 2015 13:08:38 GMT

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

Re: Where Clause in Script based on calculation

Tue, 07 Jul 2015 15:46:27 GMT

Thanks a lot, it helps!