Set Modifier strange behaviour

[#1]

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

[awhitfield]

Hi Paul,

Is the code actually in the QVW as 0031?

[#3]

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!

[venkatraju]

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)

[awhitfield]

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

Andy

[#6]

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.

[robert_mika]

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?

[awhitfield]

HI Paul,

are you able to post the qvw?

Andy

[#9]

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

[#10]

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.