Problem in Per Row Computation

[jenny143]

hi im having a problem with my query
i transfer my data from calc to base

in my calc im having a data (Image below)

To get the Current Price

Code: Select all

((E1*B1)+(A2*B2))/ C2 to get the 2nd row =6.8

I think i need a Per Row Query for this
[(Out (Row-1))*(Price(Row-1))+(Qty*Price)]/Stock

i wonder if the previous row is exist in query?????

[eremmel]

No you can not refer to the previous row in that way. SQL is pure row oriented. But you can use your table multiple times in the same query each with a different purpose. The challenge is how to relate the current row with its previous one and make it also work for the very first row.

But I'm thinking when I answer this, that you might even have more mutations to consider; you might need a total different approach. In Calc it is very natural to have running-sums in your overview. In a database you normally store only the 'mutation'-transactions. When you need a balance you do that by running a query/report over your 'mutation'-transactions. This can be quite expensive when you have million of transactions per product and this can be complicated. You have to store the mutation-transactions in the right order, do not back-track before the last time the stock was 0.0 etc.

Recently there was post related to this. Villeroy made a nice example database for that. You might check it out and see what it brings you.

[jenny143]

thank's for your reply

but the sample query in the url is for sum for all columns

im thinking for adding a current timestamp. but i don't know how i can use it...

a sequence will work for this?

[eremmel]

The things you try to achieve are not trivial and might need some complex SQL code and SQL knowledge, but when you like the challenge and want to learn that is fine. When you find it difficult to program by example you might end up in asking a lot of questions, but hopefully you learn something from this.

I think it is important to share your basic setup for a review and specify what you want and how you want to do this. For a number of issues there exist design patterns and those are not always obvious for the beginner.

E.g. you can use a sequence or identity column for you transaction ordering, but the problem is what happens when order IS important and you need to insert an extra transaction in the past (a correction). When you use a timestamp with some granularity to order your transactions and you need to insert multiple transactions at the same timestamp (this is more a theoretical issue in this case I think, but in my world it is very common).

So I advice you to have a demo-version of your application (Base document) that you can share (attach to your posts) so others can help to solve your issues my looking at it and give advice, for it is difficult to specify all questions in a clear way to be understandable for technical people.

Suggestion is to share your data model by attaching your base document and give the way you what to calculate your price and what you think your query should look like.

[Villeroy]

Getting previous and next from a database can be difficult but it is very easy to do in a spreadsheet.
Create a query which pulls the wanted columns in the right order of rows (the next after the previous)
Register your database (Tools>Options>Base>Databases)
Open a spreadsheet and hit F4
Drag the query icon from the left pane into your sheet.
This creates an import range named "Import1".
Data>Define... select "Import1", hit [More Options]
Check all the additional options.
Hit [Modify] and [OK]

"Import1" refers to some rectangle of cells, say A1:F99, where the first row keeps the column names and range A2:F99 keeps the data. Data>Refresh refreshes the import range. When you close and reopen the file you will be prompted to refresh the import ranges because the imported data are not stored in the sheet.
Put some header in G1 and your formula in G2. Copy down the formula down the column until the end of the list.
You may also add some formulas to the adjacent row below the import range. Whenever the import range is refreshed, the formula ranges will adjust to the new area of the import range.
All you need to do is opening the spreadsheet document and confirm that you want to refresh the import.

[jenny143]

thanks for the reply,


how can i copy the last row of that spreadsheet to my database even if that spreadsheet is changing??
i need this because... the user need to know the current price of the equipment (Last Current Price)

Query to Spreadsheet to Query??

[eremmel]

In this document I found the following quote:

Editing data sources
Some data sources (such as spreadsheets) cannot be edited in the data source view.
In editable data sources, records can be edited, added, or deleted. If you cannot save your edits, you need to open the database in Base and edit it there; see “Launching Base to work on data sources”. You can also hide columns and make other changes to the display.

So it looks like it is possible to update data back. But I've no experience with calc and external data sources.

An other possibility, depending on the frequency of updating the external datasource, is to export your calc and import it into your database by replacing the complete table.

[jenny143]

hi, after my research... i cant hold data from base to calc to base

and research from other site about this...
https://forums.oracle.com/forums/thread ... D=10370278
this is same as i want to accomplish
but i cant analyze very well

[eremmel]

Hi Jenny, the SQL you are referring to in the oracle post is really advanced SQL. It contains analytic SQL features that are only available in the commercial databases (as far as I know) like Oracle, MS SQLServer, DB2. The implementations between vendors is to some extend a standard, but the Oracle example contains some native functions. I tried to understand what will happen when the inventory drops to 0, but I'm not sure what will happen in that example.
As you can see a database is in many cases is a good replacement for a spreadsheet, but some 'simple' things become really complicated.
I'm working at an ERP Software vendor, and 20 years ago we could not solve the inventory price with pure SQL. So there is a mix between night batched that calculate the moving price and the last minute mutations are calculated when a clerk examines the price online.
You have a real challenge here to solve. When you need more administration tasks to automate, you might have a look to a simple open source ERP software package.

[jenny143]

i use union all for the transaction and multiply -1 to my sales
but the unit cost in my purchase period is not applicable in Union all, because my sales doesn't have cost in input data
i really need more time to solve this kind of query