topic Re: Expression returning nulls! in QlikView

Expression returning nulls!

Tue, 12 Jun 2012 15:44:40 GMT

Good afternoon, I have a problem with an expression I am using in a flat table, There is a field that has a value of either 1 or 2 for each BP, without a listbox restricting either 1 or 2 neither will show and returns a value of '-' a null.

I tried the Expression :-

if(Meter_Reading_Reason='01' and Meter_Reading_Type='01' or

Meter_Reading_Reason='02' and Meter_Reading_Type='01' or

Meter_Reading_Reason='01' and Meter_Reading_Type='02' or

Meter_Reading_Reason='02' and Meter_Reading_Type='02',BP)

I have tried to remedy the problem with the expression below (which doesn't work either)

if(Meter_Reading_Reason='01' and Meter_Reading_Type='01' and Meter_Reading_Reason<>'02' and Meter_Reading_Type<>'02' or

Meter_Reading_Reason='02' and Meter_Reading_Type='01' and Meter_Reading_Reason<>'01' and Meter_Reading_Type<>'02'or

Meter_Reading_Reason='01' and Meter_Reading_Type='02' and Meter_Reading_Reason<>'02' and Meter_Reading_Type<>'01'or

Meter_Reading_Reason='01' and Meter_Reading_Type='02' and Meter_Reading_Reason<>'02' and Meter_Reading_Type<>'01',BP)

Meter reading type and meter reading reason vary over different meter reads, they can be 1 or/and 2. What I am trying to achieve is to load Value 'BP' if either Meter reading reason or meter reading type fit these criteria.

Regards.

Paul.

Re: Expression returning nulls!

Tue, 12 Jun 2012 16:35:38 GMT

Paul:

I guess there is some problem in the data. Can you share a sample of your application to debug?

Regards,

Kiran Rokkam.

Re: Expression returning nulls!

Wed, 13 Jun 2012 08:51:09 GMT

Hi Kiran, there is no error in the code, the most part of the code is associative table link such as Customer<=>Contract<=>Property<=>Meter<=>Meter_Readings.

Have I written the expression incorrectly, I am trying to write an expression where if Meter reading reason = 1 or 2 and meter reading type = 1 or 2 then return me customers (BP) that have these types of reads on their accounts, if they have meter reading reason/type other than 1 or 2 I dont want them.

Paul.

Re: Expression returning nulls!

Wed, 13 Jun 2012 09:01:37 GMT

There is no syntax (or logical) mistake in your expression as far as I can see. Since the condition is not satisfied or for the satisfied records there are no BP, I assumed some discrpency is the data against your assumption.

Just to confirm, are your fields "Meter_Reading_Reason" and "Meter_Reading_Type" numeric or string? If they are numeric the expression will always return null as the cases are not satisfiled.

The better way to debug is select one meter transaction where the condition is satisfied and check out what Only(Meter_Reading_Reason) and Only(Meter_Reading_Type) are returning. Accordingly change you expression.

Regards,

Kiran.

Re: Expression returning nulls!

Anonymous — Wed, 13 Jun 2012 09:03:31 GMT

Not super sure what you're trying to do but if you want to sum up the BP values where Reading/Reason are either 1 or 2 you could try the following set analysis expression:

sum({$<Meter_Reading_Reason={1,2}, Meter_Reading_Type={1,2}>} BP)

Re: Expression returning nulls!

Wed, 13 Jun 2012 09:30:44 GMT

I just tried exactly that myself, it pulls all of the accounts with data other than 01 or 02 for meter reading type or reason, if it helps I am trying to exclude meter reading type'03', I tried <>'03' in the set analysis but it didnt like it, is there a way to exlude specific data out of set analysis?

The goal of the exercise is to only load accounts where meter reading reason/type and to ingore the other accounts.

Re: Expression returning nulls!

Wed, 13 Jun 2012 09:39:00 GMT

Exlusion would be Meter_Reading_Reason-={'03'}

Regards,

Kiran Rokkam.

Re: Expression returning nulls!

Wed, 13 Jun 2012 09:45:24 GMT

Erm, it didnt like - and = next to each other

Re: Expression returning nulls!

Wed, 13 Jun 2012 10:24:33 GMT

Think I got the answer I need, This returns a 0 if conditions are met oddly enough, this allows me to filter the accounts I need.

sum( {$<Meter_Reading_Reason={01,02}, Meter_Reading_Type = {'03'}>} BP)