Does function order matter when applying 2 to one field in the script?

[#1]

Does the order of expressions matter when applying in the script? My original post I discovered that the text function was not working correctly when it was inside of the rtrim function. Qlikview seemed to ignore the fact that I was telling it to consider the field only as a string.

I went through the scripts and everywhere that I had rtrim(text(field)) I changed to text(rtrim(field)) and this seemed to work correctly so that it now did not combine values with a leading 0.

However I am now having problems with fields with spaces. I have Verbatim turned on (I only need to keep leading spaces and drop trailing spaces hence the rtrim() function)

It now seems like it is ignoring the rtrim function. This is causing a lot of my formulas to calculate incorrectly giving me incorrect data. I know this is the cause because I tested the field in the formula by applying rtrim() around it.

Is there anything I can do here? Qlikview seems to only recognize the outside function and I am going crazy trying to figure this one out.

[#2]

Hi,

You calculations are not working because of the leading spaces and you considering it as Text.

Thanks

Fahad

[#3]

Hi Fahad,

Can you please explain what you mean?

Our ERP software considers leading spaces as valid and drops all trailing spaces.

It also considers leading '0's as valid if they are in an nvarchar field.

Therefor the following would be considered unique fields through out the system and by default Qlikview would see only 2 values. (I will user | to show where the field starts and ends to show spaces):

|U613X000P0218|

| U613X000P0218|

|5558698|

|05558698|

To get Qlikview to show these all as unique I had to turn verbatim on. I quickly discovered that some of the fields pulled where stored to the database with trailing spaces also intact. I needed this to behave like our software so I had to then apply rtrim() to every nvarchar to make sure no trailing spaces got pulled. To fix the issue with nvarchar fields containing only numbers I had to also apply text() to the same fields.

[#4]

Hi Marc,

When you load data using Verbatim, Text(rtrim()) function in the script the data loads as String and stores as text.

When you use these values in any calculations or formulae it would not work because it is text.

In your first post you said ""I know this is the cause because I tested the field in the formula by applying rtrim() around it."

This is because

when you again manuallly use Rtrim(), it converts the value to number datatype and calculates.

Solution could be

Use Rtrim() and Ltrim() in your formulae/calculations to take of the spaces.

Hope this helps you. send me sample data so that I could help you better.

Thanks

Fahad

[#5]

So you are basically saying that ANY place this field is located I will need to surround it with rtrim()? This makes absolutely no sense to me. Even the reference manual states:

rtrim(s )

Returns the string s trimmed of any trailing spaces.

text(expression )

The text function forces the expression to be treated as text, even if a numeric interpretation is possible.

These seems ridicules that I need to research every field to find out if it is a nvarchar and then in every instance add this function. It is bad enough the Verbatim is limited to all spaces leading and trailing.

Why would  Rtrim convert it to a number?

This seems like a Major downfall to Qlikview if it takes this much work to make sure the data is correct.

[#6]

Another question. In our software the following is possible. I know that it is bad practice, but it still can happen. Again I will use | to show start and end so spaces are more visible.

|123456|

| 123456|

|0123456|

| 0123456|

All of the above would be considered unique if they are in a nvarchar. I believe by default Qlikview would see only

|0123456| as being unique. I would assume that Verbatim would pull 2 values, but it is possible that the data base has trailing spaces:

|0123456  |

| 0123456  |.

Since tables there are linked via this field, without verbatim I would get incorrect results. An example could be Part cost. Our system stores these in a separate table that is linked via Part Number. Therefor our system has 4 unique Part costs, which qlikview returns all 4 but sees just 1 part causing all the costs to be off.

What would be the best practice in this case? It needs to be completed in the script due to the fields being key fields that I need to link on.

Edit: I am entering these actual parts into our test system to see how Qlikview is behaving exactly. I will update again when I have the results.

[#7]

Data Entered in system (quotes for visual):

Part	Cost
'123456'	5
' 123456'	10
'0123456'	15
' 0123456'	20

Results of Test (Straight Table with Dimension Part and sum(Cost):

Default Qlikview Settings:

I was correct with my understanding, this states that there is only one part with incorrect cost of 50

Part	Cost
'0123456'	50

Set Verbatim=1;  and apply Rtrim() to our string fields (This is what our system does internally):

I was apparently wrong with this one. It is still incorrect, just removes the leading 0?

Part	Cost
'123456'	50

Set Verbatim=1;  and apply Rtrim(text()) to our string fields to try and tell it to see only text format:

This apparently did nothing for the results..

Part	Cost
'123456'	50

Set Verbatim=1; Reversed function to show text(rtrim()):

Somehow it worked in this case...It is not working for me in my actual dashboard with other data however.

Part	Cost
'123456'	5
' 123456'	10
'0123456'	15
' 0123456'	20

Now what is odd is that I am using this last method in the document that I noticed the issue.

I have the following expression:

sum(if(left(([Segment 1]),1)<>'1' and left(([Segment 1]),1)<>'2' and left(([Segment 1]),1)<>'3' and [Segment 1] = '700400', [Credit Amount]+[Debit Amount]))

With the last example this formula fails and returns 0, UNLESS I select the Value 700400.

However if I apply rtrim() again to the field (this field is resident loaded from the original table that has the text(rtrim()) functions called) it calculates correctly.

I feel like I am going in circles now and I am not sure what I should do... before adding rtrim for the second time I tried this in a text box ='@' & [Segment 1] & '@' to try and catch an issue, but there are no leading or trailing spaces in this field. after adding the rtrim the results are the same, however the expression in issue magically works.

It is critical that I trust I am getting the correct results as this is a Financial Dashboard that is used by many to track our company.

[#8]

Isn't rtrim considered a String function? I was also assuming that the text function and a string field are the same thing?

[MayilVahanan]

Hi

rtrim() function trim the trailing space in the string n number.. functionality to trim the space.

Can you post a sample file ?

[#10]

Due to it being financial and quite large I do not think I will have time today as I need to figure out which fields need to be scrambled and data reduced. I am just confused over the whole situation with how rtrim(text()) seems to nullify the text function