Negative Points for If(IsNull(...

craig157 — Thu, 20 Jul 2017 10:53:59 GMT

Hi All,

I am trying to create a report that will hopefully show which of our customer records will need various improvements.

Essentially I want to create a Ranking Table.

So I have setup multiple variables as below:

vMaxRankingPoints = 10

vNullCompanyName = -1

vNullAddressPostcode = -1

vNullCounty = -1

vNullTelephoneNumber = -1

And so on and so on.

I've set these up as Variables as in future I would like to be able to change the values for putting a push on getting certain fields improved.

So the end goal is to find out how many records have landed within certain 'Ranking Points' so we can investigate why they have been neglected and such.

Each field has a relevant 'ID' which I want to allocate a ranking too - and then check the fields for Null values and remove Ranking Points accordingly.

I've tried doing like this expression below but had no joy and not sure what type of Function I should be looking for:

aggr(cOrgID > 0, vMaxRankingPoints)

AND If(IsNull([CompanyName]),vNullCompanyName)

AND If(IsNull([Address1]),vNullAddress1)

AND If(IsNull([AddressCounty]),vNullAddressCounty)

AND If(IsNull([AddressPostcode]),vNullAddressPostcode)

AND If(IsNull([AddressTown]),vNullAddressTown)

AND If(IsNull([Tel1]),vNullTelephone)

AND If(IsNull([modeled_turnover_band_desc]),vNullTurnover)

AND If(IsNull([WWW]),vNullWebsite)

Hopefully this would give me an end result per ID like: ID = 003449002 Ranking = 7

The goal after this would then be to group these so I could have a count of how many companies per Ranking Point.

Sorry if I've got carried away but just trying to cover all basis.

Cheers

Re: Negative Points for If(IsNull(...

sunny_talwar — Thu, 20 Jul 2017 11:08:07 GMT

I think you are looking to add these points and the way it is structured right now, it won't add... try something like this

RangeSum(

If(cOrgID > 0, vMaxRankingPoints),

If(IsNull([CompanyName]), vNullCompanyName),

If(IsNull([Address1]),vNullAddress1),

....

)

Re: Negative Points for If(IsNull(...

craig157 — Thu, 20 Jul 2017 12:14:37 GMT

Hi Sunny!

That worked absolutely perfect!

Thanks for your help on this

topic Re: Negative Points for If(IsNull(... in QlikView

Negative Points for If(IsNull(...

Re: Negative Points for If(IsNull(...

Re: Negative Points for If(IsNull(...