Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gfisch13
Creator II
Creator II

Even Dollar Amounts

Good morning folks - I'm working with a file of purchase orders and want to identify only those that have even amounts, for instance, $10,000, $50,000, $1,250,000, $6,000,000.   I've searched these boards but have yet to hit on what I'm looking for.   Is there an easy way to do this.  In other applications I've seen this called Round Amounts or Flat Dollar Amounts.  Just not sure what to call it here.  

Appreciate the guidance.  Thanks!

George

Labels (3)
1 Solution

Accepted Solutions
edwin
Master
Master

What would be the business rules in selecting some over the others?  why is 5002510 not selected?  is it because it is not in 1000s?  you can use round function this way:

 

where round([PO Value],1000) = [PO Value]

View solution in original post

16 Replies
edwin
Master
Master

you can use round function:

round(11700,1000) is 12000 (rounded off to the nearest 1000.  set the step value if you wanted it by 1000, 10000, etc..

Round ‒ QlikView

 

gfisch13
Creator II
Creator II
Author

This is close, but not exactly what I was looking for.   See the figure attached.  I'm not actually looking to do any rounding, I want to select the PO's that are at certain thresholds.  In this figure I want to be able to look at the ones highlighted in yellow.   I'm thinking I need to do this in the script.......

FlatDollarExample.JPG

edwin
Master
Master

What would be the business rules in selecting some over the others?  why is 5002510 not selected?  is it because it is not in 1000s?  you can use round function this way:

 

where round([PO Value],1000) = [PO Value]

edwin
Master
Master

just change the step depending on what your business rule is (1000, 10000, etc)

edwin
Master
Master

or a more readable manner:
where floor(POValue/1000)*1000 = POValue

gfisch13
Creator II
Creator II
Author

Thank you Edwin - appreciate the responses.  I'm doing some fraud testing and we are trying to detect PO's where the amount is a flat amount as I showed since those can have a higher risk of being repetitive and sometimes users can break a PO into pieces to circumvent approvals/hide spend.

As I see you are using the 'where', isn't that a statement that would be reserved for and SQL load?

gfisch13
Creator II
Creator II
Author

Thanks Edwin - I removed the where from the script and it worked perfectly.  Exactly what I was looking for!!

edwin
Master
Master

i added the where clause as i was expecting you would use it in the script as a filter, regardless, the principle is the same if you use it as an if statement in a field field.  doing this kind of determination in the script makes for a faster UI 

vidyut
Partner - Creator II
Partner - Creator II

You could also use substringcount(text(POValue),`0`) to get number of zeros in the POValue field, that might be interesting feature.