Forum Discussion

MartinSuske1's avatar
MartinSuske1
Qrew Member
3 years ago

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:

T_PRODUCT_PRICES
ID, NAME, PRICE, VALID_FROM, VALID_TO
1, Milk, 2.00$, 2022-01-01, 2022-03-31
1, Milk, 2.50$, 2022-04-01, 2022-09-30
1, Milk, 3.00$, 2022-10-01, <<null>>

T_ORDER
PRODUCT_ID, PRICE
1, 2.50$

=> How can I get the currently valid price and use it in a relationship with a facts table?

Are there any best practices in Quickbase?

Kind regards,
Martin


------------------------------
Martin Suske
------------------------------
formula queries
Relationships
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    3 years ago
    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
    /
    ------------------------------
    • MartinSuske1's avatar
      MartinSuske1
      Qrew Member
      3 years ago
      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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        3 years ago
        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
        ------------------------------