Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cluscombe
Contributor III
Contributor III

Loading a field with percentages and numbers

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

5 Replies
luismadriz
Specialist
Specialist

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

cluscombe
Contributor III
Contributor III
Author

Unfortunately (for my code, good for the business)  I often have ROI that exceed 1000%, so doesn't look like your suggestion would work in this case.

luismadriz
Specialist
Specialist

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

cluscombe
Contributor III
Contributor III
Author

Only solution I came up with is to have the users clean up the historical data and then put some input controls in place for future records.  Really surprised couldnt's come up with a solution to this.

Anonymous
Not applicable

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/StringFunction...