Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

craig157
Contributor

Negative Points for If(IsNull(...

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

1 Solution

Accepted Solutions
MVP
MVP

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

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),

....

)

2 Replies
MVP
MVP

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

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),

....

)

craig157
Contributor

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

Hi Sunny!

That worked absolutely perfect!

Thanks for your help on this