topic Re: Even Dollar Amounts in QlikView

Even Dollar Amounts

gfisch13 — Sat, 16 Nov 2024 17:44:59 GMT

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

Re: Even Dollar Amounts

edwin — Thu, 03 Dec 2020 18:22:37 GMT

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

Re: Even Dollar Amounts

gfisch13 — Thu, 03 Dec 2020 18:46:19 GMT

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

Re: Even Dollar Amounts

edwin — Thu, 03 Dec 2020 19:50:01 GMT

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]

Re: Even Dollar Amounts

edwin — Thu, 03 Dec 2020 19:51:04 GMT

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

Re: Even Dollar Amounts

edwin — Thu, 03 Dec 2020 19:52:29 GMT

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

Re: Even Dollar Amounts

gfisch13 — Thu, 03 Dec 2020 20:07:55 GMT

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?

Re: Even Dollar Amounts

gfisch13 — Thu, 03 Dec 2020 20:37:05 GMT

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

Re: Even Dollar Amounts

edwin — Thu, 03 Dec 2020 20:40:06 GMT

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

Re: Even Dollar Amounts

vidyut — Thu, 03 Dec 2020 21:25:53 GMT

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

Re: Even Dollar Amounts

gfisch13 — Thu, 03 Dec 2020 21:48:54 GMT

Thank you Vidyut - this will help me putting these values into buckets for further filtering. Appreciate the input.

Re: Even Dollar Amounts

edwin — Fri, 04 Dec 2020 12:27:53 GMT

@vidyut thats an interesting view which didnt come to mind however, it will count zeroes no matter where it is:
100000111

Re: Even Dollar Amounts

vidyut — Sun, 06 Dec 2020 21:09:21 GMT

@edwin yes, you are correct. I was just stressing the point that in case you want to extract more features, start looking at the number as string, that may give you some interesting options.

In this case, to count the trailing zeros, if the whole dollar amount is available in field txtNum, an expression of the form len(txtNum)-[hidden function call on txtNum] would return the number of trailing zeros.

Can you guess what is this function? I could give the solution, but thought it would be a fun exercise. 🙂

Re: Even Dollar Amounts

edwin — Mon, 07 Dec 2020 12:25:45 GMT

@vidyut interested in your mystery function. could that find magnitude of the number (K,M,B,...)? could be a new thread

Re: Even Dollar Amounts

vidyut — Mon, 07 Dec 2020 13:39:21 GMT

@edwin

ok, I give you the answer, If you use len(txtNum) - findOneOf(txtNum,`123456789`,-1), You should get the exact number of Trailing Zeros , which was the feature we were after.

@gfisch13 , Try incorporating this one, and let me know your interesting finds 🙂

Cheers

Re: Even Dollar Amounts

edwin — Mon, 07 Dec 2020 22:06:26 GMT

lets not talk about decimal places rt? : )

Re: Even Dollar Amounts

vidyut — Sat, 12 Dec 2020 22:13:03 GMT

Depends on how you have the data stored. You have a valid point though 😊