topic Re: Set Modifier strange behaviour in QlikView

Set Modifier strange behaviour

Tue, 10 Mar 2015 10:48:59 GMT

I have a chart of accounts and I am using set analysis to get the balance for specific accounts. The account codes are strings of four numbers, for example 0031, 1122, 2201.

My expression is quite simple:

sum({$<AccountNumber={0031}>} Balance)

but this doesn't work, it shows a balance of 0.00.

However if I change it to:

sum({$<AccountNumber={'0031*'}>} Balance)

this gives the correct balance, and so does this:

sum({$<AccountNumber={'>=0031 <=0031'}>} Balance)

I don't understand why the first expression doesn't work. Any ideas? Thanks, Paul

Re: Set Modifier strange behaviour

awhitfield — Tue, 10 Mar 2015 11:03:07 GMT

Hi Paul,

Is the code actually in the QVW as 0031?

Re: Set Modifier strange behaviour

Tue, 10 Mar 2015 11:08:43 GMT

Well I believe so. If I just do a straight table with AccountNumber as the dimension and sum(Balance) as the expression, it shows me all the account numbers including 0031. Looking at it though, the alignment of AccountNumber is to the right, which would indicate that QlikView is treating it as numeric since I haven't changed the defaults in the Presentation tab. But then if it wasn't numeric I wouldn't expect it to show the leading zeroes. Confused!

Re: Set Modifier strange behaviour

venkatraju — Tue, 10 Mar 2015 11:13:34 GMT

Hi Paul,

I think qlikview is reading AccountNumber data as text , if that is the case ,modifier comparison values should be in single quotes as text values should be kept in ''.

Try with this expression sum({$<AccountNumber={'0031'}>} Balance)

Re: Set Modifier strange behaviour

awhitfield — Tue, 10 Mar 2015 11:18:42 GMT

Just forced the account number to be text in the attached, but this still works without the quotes.

Andy

Re: Set Modifier strange behaviour

Tue, 10 Mar 2015 11:20:47 GMT

Hi Venkat, I tried that and it does not display the account number or balance. I tried with both single and double quotation marks. I should have stated in my original question when I said the balance was 0, that it was not showing the account number here either, the zero balance was in the total.

In my script I am loading "Text(AccountNumber) as AccountNumber" so QlikView should be treating it as text.

Re: Set Modifier strange behaviour

robert_mika — Tue, 10 Mar 2015 11:25:09 GMT

Qlikview assigns dual value (Text/Number) for data so in this case probably does not matter if is being read as Text or Number as the calculation will be done anyway,

Paul could you attached your data?

Re: Set Modifier strange behaviour

awhitfield — Tue, 10 Mar 2015 11:30:39 GMT

HI Paul,

are you able to post the qvw?

Andy

Re: Set Modifier strange behaviour

Tue, 10 Mar 2015 11:40:14 GMT

Not in its current form but I will try to set up a simpler version where I can repeat the problem.

Re: Set Modifier strange behaviour

Tue, 10 Mar 2015 13:10:17 GMT

I set up a simpler version and couldn't replicate the problem. After playing around with the load script, I noticed that the Accounts table consisted of not only the accounts but a long list of question marks.

I then changed "Load Text(AccNumber) as AccountNumber" to "Load AccNumber as AccountNumber" and that fixed the problem. I'm still not entirely sure of the reason but at least it works now! Thanks for the help.

Re: Set Modifier strange behaviour

awhitfield — Tue, 10 Mar 2015 13:32:35 GMT

Good stuff, glad that it's sorted.