Slowly Changing Dimensions
Hello Community, I need to be able to have different versions of parameters in a dimension table, depending on a given time period. I wonder how to solve this in Quickbase. Simplified example: ...
Forum Discussion
Martin -
You'll probably want to have a Products table as a parent to the Product Prices table. Your "Current Price" would be a summary field from the Product Prices to Products. Most likely, you'd also want a Line Items table as a child to an Order table. Then, you would have Line Items as a child of the Products table and bring over that "Current Price" as a lookup.
------------------------------
Blake Harrison
bharrison@datablender.io
/
------------------------------
You'll probably want to have a Products table as a parent to the Product Prices table. Your "Current Price" would be a summary field from the Product Prices to Products. Most likely, you'd also want a Line Items table as a child to an Order table. Then, you would have Line Items as a child of the Products table and bring over that "Current Price" as a lookup.
------------------------------
Blake Harrison
bharrison@datablender.io
/
------------------------------
Hi Blake,
thanks for your feedback. My example lacks some other tables because I wanted to focus on the problem of filtering out the currently valid row from "Product Prices".
Your idea of summarizing it up to a "Products" table would help if I could put a filter on the summary and only use rows that are within the current time range, which would be the 2.50$ row for today is between 2022-04-01 and 2022-09-30.
I just checked and there is a filter setting for summary fields. But I guess I'm not able to apply a dynamic date formula there (?)
Another problem I have is that I would need to be able to sell products "in the future". Using the example model again I should have added a "Sales Date" Column to my Order table.
T_ORDER
PRODUCT_ID, PRICE, SALES_DATE
1, 2.50$, 2022-10-01
So if I sell Milk today (2022-09-12) with a future sales date of 2022-10-01, I would need to retrieve the 3.00$ price row which applies for that period.
I think it might be necessary to build a key that somehow contains the different date ranges in "Product Prices" and that can be matched against a key from the Orders table. But since on the Order side I have a single date and in "Product Prices" I have multiple date ranges, I'm not sure how a comparison of those keys could work.
Any further ideas are highly appreciated.
Kind regards,
Martin
------------------------------
Martin Suske
------------------------------
thanks for your feedback. My example lacks some other tables because I wanted to focus on the problem of filtering out the currently valid row from "Product Prices".
Your idea of summarizing it up to a "Products" table would help if I could put a filter on the summary and only use rows that are within the current time range, which would be the 2.50$ row for today is between 2022-04-01 and 2022-09-30.
I just checked and there is a filter setting for summary fields. But I guess I'm not able to apply a dynamic date formula there (?)
Another problem I have is that I would need to be able to sell products "in the future". Using the example model again I should have added a "Sales Date" Column to my Order table.
T_ORDER
PRODUCT_ID, PRICE, SALES_DATE
1, 2.50$, 2022-10-01
So if I sell Milk today (2022-09-12) with a future sales date of 2022-10-01, I would need to retrieve the 3.00$ price row which applies for that period.
I think it might be necessary to build a key that somehow contains the different date ranges in "Product Prices" and that can be matched against a key from the Orders table. But since on the Order side I have a single date and in "Product Prices" I have multiple date ranges, I'm not sure how a comparison of those keys could work.
Any further ideas are highly appreciated.
Kind regards,
Martin
------------------------------
Martin Suske
------------------------------
- MarkShnier__You
Qrew Legend
Martin,
I believe that this can be solved using a formula query. But since the syntax for that is a bit complex it would be beyond the scope of what I could work on through this forum in part because I wouldn't have the visibility into your actual application.
The basic approach would be to do a formula query which would look for a match on the item and where the date on the line item of the sale falls between the beginning and ending date of the prices table. We probably would also have situation where you have prices loaded into the prices table with no end date as your supplier has not announced a end date to the current price. So the formula Quarry would have to get hits on those two.
It is also theoretically possible with this design that you end up having two prices entered which meet the date criteria. I either could be a data entry error and that could exist. In that case we would just have a formula to grab off the first price that it finds which qualifies.
If you have a small budget for an hour or two of consulting time then please feel free to contact me directly using the email address in my signature line and we will get this working. Alternatively you can study up on formula queries and post what you have so far if you get stuck and we can slowly solve it on this forum.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------- Hi Mark,
sorry for the late response. The requirement got changed so that it was no longer necessary to automatically join products and prices.
The altered use case has the user always select from a list of possible conditions for a certain product thus I could easily use the filter condition on the related field in Quickbase to get the currently valid entries using your formula query approach.
Thanks for your help on this topic.
Best regards,
Martin
------------------------------
Martin Suske
------------------------------
