[kmymoney] [Bug 473322] SLQLite "kmmSplits"."sharesFormatted" Data Corrupt perhaps??
https://bugs.kde.org/show_bug.cgi?id=473322 --- Comment #11 from Jack --- Not a problem. It's always hard to tell how much programming experience anyone has, but I figure sql is not a trivial thing to learn, so if you've gotten that far, you can probably figure out more :-) I'll leave it to you to search for how to write functions in Python (or whatever) that you can call in sqlite, but you can also define functions totally within sqlite, which at least can make your code look cleaner. Another option would be to write everything in some other language (Perl, Python, ...) and use it's interface to read the data from sqlite. It really boils down to personal preference. -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 473322] SLQLite "kmmSplits"."sharesFormatted" Data Corrupt perhaps??
https://bugs.kde.org/show_bug.cgi?id=473322 --- Comment #10 from JAH --- Well I'm very grateful for your consideration and raising the wish list entry. Sorry I misunderstood your point about the function. As a complete amateur I did try an extensive search for a solution / function but only came up with the explicit parsing which though messy does work. Thanks again John -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 473322] SLQLite "kmmSplits"."sharesFormatted" Data Corrupt perhaps??
https://bugs.kde.org/show_bug.cgi?id=473322 Jack changed: What|Removed |Added See Also||https://bugs.kde.org/show_b ||ug.cgi?id=473381 --- Comment #9 from Jack --- The problem is that Formatted is not a numeric field, it is explicitly text, and formatting of currency amounts can reasonably include decimal and thousands separators. It also turns out it will likely be significantly more difficult to make that change than I first thought - I was thinking about a different part of the program. I won't rule it out, but it probably won't happen soon. I have opened a separate wishlit so it doesn't get forgotten. See https://bugs.kde.org/show_bug.cgi?id=473381 I wasn't asking if you could explicitly parse out the commas, I was asking if there was an existing function to convert text to number, recognizing the commas, just so you don't need to parse them out. In my reading (I don't commonly use sqlite, so I'm not really familiar with it) it seems you should relatively easily be able to write a function in some other language (where the parsing and/or conversion would be easier) that you can then call directly from sqlite. -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 473322] SLQLite "kmmSplits"."sharesFormatted" Data Corrupt perhaps??
https://bugs.kde.org/show_bug.cgi?id=473322
--- Comment #8 from JAH ---
>>"I'll see if it's reasonable to add a checkbox to toggle the use of the
>>commas" -
That would be great if possible because as you say, I don't believe they have
any place in a numerical field. Surely it's more properly the program's job to
the formatting
>>Are you sure there is no function in sql to parse a number from a string that
>>does have thousands separators?
They can indeed be parsed out but it makes for unnecessarily messy code.
>>...process that file to read numbers with the thousands separator?
Yes but I'm not just reading & copying data, I need to perform calculations
which is where the commas interfere.
>>Are you depending on the number of decimal places output, or just depending
>>that there are enough to not lose information?
Enough to be accurate to 2 decimal in the result but that sometimes requires
more places in the price
... have you thought about using the unformatted version? in the form of
"x/y"
That is my current choice but here is an example of the in (sqlite) sql code to
parse one value stored as a fraction:
((CAST(substr ("kmmSplits"."shares", 1, (instr ("kmmSplits"."shares", '/' ) - 1
) ) AS REAL ) ) / (CAST( substr( "kmmSplits"."shares", ( instr(
"kmmSplits"."shares", '/' ) + 1 ), LENGTH( "kmmSplits"."shares" )) AS REAL )))
I sometimes want to multiply several values whilst adding and subtracting
others .
Thanks to your fix, in future it can be replaced with
"kmmSplits"."valueFormatted" but that will have to be
REPLACE("kmmSplits"."valueFormatted",',','')) if it contains commas. Hence my
request.
--
You are receiving this mail because:
You are watching all bug changes.
[kmymoney] [Bug 473322] SLQLite "kmmSplits"."sharesFormatted" Data Corrupt perhaps??
https://bugs.kde.org/show_bug.cgi?id=473322 --- Comment #7 from Jack --- No, given that description, I think the presence of the commas is intentional. I just didn't notice it. I'll see if it's reasonable to add a checkbox to toggle the use of the commas, but I'm not sure at this point. In the meantime, some questions: Are you sure there is no function in sql to parse a number from a string that does have thousands separators? If you are just writing out those values as part of a csv file, is it possible tor whatever you are using to process that file to read numbers with the thousands separator? I haven't tried, but I would think most languages (including Excel/Libre Office) should have a way to do it. This might mean less processing in sql and more in subsequent steps. Are you depending on the number of decimal places output, or just depending that there are enough to not lose information? As a possible alternative, have you thought about using the unformatted version? It is always (I think) in the form of "x/y" where x and y are integers. Just do the division. -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 473322] SLQLite "kmmSplits"."sharesFormatted" Data Corrupt perhaps??
https://bugs.kde.org/show_bug.cgi?id=473322 --- Comment #6 from JAH --- Thanks for your kind reply (and work on kmymoney). I use the sqlite3 command to process preprepared .sql queries that produce .csv reports with calculated ratios etc. I noticed that prices (over 1000) stored in the "kmmPrices"."priceFormatted" field for example, include a comma as the thousand separator, which has to be parsed out for sql calculations. These ."Formatted" would be much easier & neater to use in sql calculations but can't be relied upon "raw" if some include commas etc - hence my add on question. (The alternative of parsing fractions for substrings does require some very very long expressions in some instances.) I had not previously reported it as a bug because I inferred from the manual that this formatting was part of the "xFormatted" fields purpose - to improve the readability of large amounts on screen (but hoped there might be a settings option to turn it off - that I had missed) >From the handbook: "Monetary amounts and share values are shown both in numerator/denominator format, and, with a field name suffixed with 'Formatted', in the form as shown on your screens." Perhaps in light of your last comment I should have reported it earlier as a bug? -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 473322] SLQLite "kmmSplits"."sharesFormatted" Data Corrupt perhaps??
https://bugs.kde.org/show_bug.cgi?id=473322 --- Comment #5 from Jack --- The ONLY change I made was in the name of the variable being formatted. I don't believe thousand separators (commas) should ever be used in such variables. I'm not aware of any KMM setting that should have made them be used. What are you using to process this data? In terms of stability of the current status, I don't foresee any short term work on the SQL storage plugin other than to fix bugs. -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 473322] SLQLite "kmmSplits"."sharesFormatted" Data Corrupt perhaps??
https://bugs.kde.org/show_bug.cgi?id=473322 --- Comment #4 from JAH --- Thanks so much for fixing this issue, it will make writing SQL queries so much easier! BTW am I right in thinking these ."valueFormatted" fields will no longer include comma thousand separators (which previously had to be parsed out for calculations)? Or perhaps that was a setting I had made in error in a previous version installation? -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 473322] SLQLite "kmmSplits"."sharesFormatted" Data Corrupt perhaps??
https://bugs.kde.org/show_bug.cgi?id=473322 Jack changed: What|Removed |Added Version Fixed In||5.1.4 Latest Commit||https://invent.kde.org/offi ||ce/kmymoney/-/commit/854ec1 ||ac9a8b8d8cff082d1a51e9978a4 ||126090b Status|REPORTED|RESOLVED Resolution|--- |FIXED --- Comment #3 from Jack --- Git commit 854ec1ac9a8b8d8cff082d1a51e9978a4126090b by Jack Ostroff. Committed on 13/08/2023 at 19:41. Pushed by ostroffjh into branch '5.1'. Fix sql sharesFormatted to use shares instead of price FIXEDIN: 5.1.4 M +1-3kmymoney/plugins/sql/mymoneystoragesql_p.h https://invent.kde.org/office/kmymoney/-/commit/854ec1ac9a8b8d8cff082d1a51e9978a4126090b -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 473322] SLQLite "kmmSplits"."sharesFormatted" Data Corrupt perhaps??
https://bugs.kde.org/show_bug.cgi?id=473322 --- Comment #2 from Jack --- Git commit 6641cad7c940a0867fd106e14bebba00d51bdf4c by Jack Ostroff. Committed on 13/08/2023 at 19:25. Pushed by ostroffjh into branch 'master'. Fix sql sharesFormatted to use shares instead of price M +1-3kmymoney/plugins/sql/mymoneystoragesql_p.h https://invent.kde.org/office/kmymoney/-/commit/6641cad7c940a0867fd106e14bebba00d51bdf4c -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 473322] SLQLite "kmmSplits"."sharesFormatted" Data Corrupt perhaps??
https://bugs.kde.org/show_bug.cgi?id=473322 --- Comment #1 from Jack --- Ouch. You are right, sharesFormatted does appear to be the price, formatted to a precision dependent on the account of the split. I'll have to dig into the code to see what's going on. -- You are receiving this mail because: You are watching all bug changes.
