11 Replies Latest reply: Nov 19, 2012 10:14 AM by Marc Livingston RSS

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

      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.

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

          Hi,

           

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

           

           

           

          Thanks

          Fahad

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

              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.

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

                  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

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

                      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.

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

                        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.

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

                            Data Entered in system (quotes for visual):

                             

                             

                            PartCost
                            '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

                            PartCost
                            '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?

                            PartCost
                            '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..

                            PartCost
                            '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.

                            PartCost
                            '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.

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

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

                              • Re: Does function order matter when applying 2 to one field in the script?
                                mayilvahanan ramasamy

                                Hi

                                 

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

                                 

                                Can you post a sample file ?

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

                                    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

                                      • Re: Does function order matter when applying 2 to one field in the script?
                                        mayilvahanan ramasamy

                                        Hi

                                         

                                        Text() function gives result in string format only..

                                         

                                        text( A ) where A=1234 returns:

                                           

                                         

                                        Result

                                        String

                                        1234

                                        Number

                                         

                                        rtrim() function

                                        rtrim(s )

                                        Returns the string s trimmed of any trailing spaces.

                                        Example:

                                        rtrim ( ' abc' ) returns ' abc'

                                        rtrim ( 'abc ' ) returns 'abc'

                                         

                                        Hope it helps

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

                                            OK, so wouldn't the function rtrim remove any spaces to the right, and then wrapping that in Text() cause the result to be a string format only? Why would

                                            Load

                                            text(rtrim(field)) as field

                                            from file;

                                             

                                            not work, but doing this would work?:

                                             

                                            Load

                                            text(rtrim(field)) as field

                                            from file;

                                             

                                            Load

                                            rtrim(field) as field

                                            resident file;

                                            drop table file;

                                             

                                             

                                             

                                            I guess a better question would be, at what point does Qlikview format a field as a number vs as a string? If I am loading an entire table to be stored into a QVD before I do any alterations or links in the current dashboard, what would be the best way to format the data so that I would not need to do repeat functions or store incorrect data into the QVD itself? My only requirements are that the nvarchar fields be treated only as strings, even if they contain only numerical data. and that all nvarchar fields retain leading spaces.

                                             

                                            I am just completely lost on the logic that allows the following:

                                            (All inside of a straight table with [Segment 1] as the dimension

                                             

                                            //Only works if I apply ONLY rtrim([Segment 1]) as [Segment 1] in the script level , or if I do NOT apply the Text() function at all, OR if I select only value 700400 from that field.

                                            sum(if([Segment 1]='700400',[Credit Amount]+[Debit Amount]))

                                             

                                            //Works no mater what I do in the script. Only works if [Segment 1] is the dimension which makes sense.

                                            if([Segment 1]='700400',sum([Credit Amount]+[Debit Amount]))

                                             

                                            //Works all the time.

                                            sum({<[Segment 1]={'700400'}>}[Credit Amount]+[Debit Amount])

                                             

                                             

                                            I know it would work for me to switch all my expressions to use set analysis. That is not my question or issue. I need to know how the data is effected when it is being stored into the QVD's and how linking between tables are being effected when I resident load from the first load of a table. This is not a simple "The function works all problems are solved" issue.