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
MarkShnier__You
Qrew Legend
Qrew LegendMartin,
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
------------------------------
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
------------------------------
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
------------------------------