5 Replies Latest reply: Nov 22, 2017 12:32 PM by Bill Markham RSS

    Loading a field with percentages and numbers

    Chris Luscombe

      Hi Everyone -

      I am loading data into a Qlik Sense application that contains a field called ROI (return on investment).  This field is manually populated in an upstream system.  Sometimes the user enters just a number (25), other times the user enters a number with a percent sign (25%). Upon loading this data I then end up with values that not comparable. I'll end up with a field that looks like:

       

      Record #         ROI

      1                    25

      2                   .25

       

      This doesnt help me since the end users intended for 25 and 25% to represent the same value.

       

      How do I control for this dirty data input to make the values comparable? Thanks!

       

      Chris

        • Re: Loading a field with percentages and numbers
          Luis Madriz

          Hi Chris,

           

          Assuming that's the typical error I would do this:

           

          I would define my limits to the ROI, let's say between -0.5 and 2 (-50% and 200%) then I would create an Updated ROI field in the loading script that, if within the limits, the same value but if not, the value / 100. At the same time I'd create a flag as UpdatedROI_Flag with 0 for the ones that you didn't update and 1 for the ones that you updated, so i'ts easy to see and troubleshoot what you did.

           

          This of course assumes you don't have have projects with 2500% ROI... well, those would be amasing projects!!!

           

          You could also create it on the chart after the loading script but it'll depend on the size of your data. I usually don't like to create chart fields with IFbut it could work... I'm talking Qlik Sense in this part. I've never see Qlik View

           

          I hope this helps,

           

          Cheers,

           

          Luis

          • Re: Loading a field with percentages and numbers
            Luis Madriz

            Hi,

             

            Just wondering to know how you went about this one, Did you manage to get some control upstream? or maybe a report to users to update accordingly?

             

            Cheers,

             

            Luis

             

            PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

            • Re: Loading a field with percentages and numbers
              Bill Markham

              You could use the PurgeChar() function in your load script to remove the % symbols

               

              Maybe some like :

               

                   num ( PurgeChar ( [ROI] , '%'  ) / 100 ) , '#o%' )

               

              That is assuming they both have same numeric interpretation.  If 25% is otherwise represented as 0.25 then you will probably need to use an If statement using the Index() function to only divide by 100 if a % character is found.

               

              https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/StringFunctions/Index.htm