ETL to Track Weekly Contracts

ChrisGaffan
ChrisGaffan Member
in Magic ETL
Capture.PNG

I'm trying to connect two datasets to track contract fills per week.

The screenshot is from one of the two datasets and it includes the contracted amounts of each commodity and the Start/End dates of the contract.

The second dataset has data pertaining to the amount of cases actually received per day (which I have converted to show as totals in relation to the corresponding week #)

What i'd like to know is if there is a way reference the Start and End Week columns in the first dataset to correspond with the totals from the second dataset, or if i'll need to convert those columns to each induvial week in that range manually. I'm trying to keep Dataset One as compressed as possible row wise to allow for subsequent contracts to be added as the months/years go on.

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Hmmm if you are doing a join based on the start and end dates, I think that may cause some issues. But if you just want a formula to check to see if a date is within the range you could use the formula tile and do something like:
    CASE WHEN `date` >= `Start Date` AND `date` <= `End Date` THEN 'Within Range' ELSE 'Not Within Range' END

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach

    Not sure I understand what you are trying to do. Could you perhaps provide a sample data table of what you would hope the output would look like and identify which columns are from which dataset? Sample data from both datasets would be helpful as well.

    If I solved your problem, please select "yes" above

  • ChrisGaffan
    ChrisGaffan Member

    @ColemenWilson Ya i think I complicated it with my long winded description. I just want to know if there is a calculated field I can make where Domo will recognize all of the dates that occur between the "Start and End Week" column dates so I can pull out Week #s to compare to. OR, will I need to take those columns and make a row for every Week # instead.

    The pictured data set is of an Excel pull with all of the contract data so I was wanting to keep that workbook's setup as simplified as possible to allow for additional contract data as the months/years go on.

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Hmmm if you are doing a join based on the start and end dates, I think that may cause some issues. But if you just want a formula to check to see if a date is within the range you could use the formula tile and do something like:
    CASE WHEN `date` >= `Start Date` AND `date` <= `End Date` THEN 'Within Range' ELSE 'Not Within Range' END

    If I solved your problem, please select "yes" above

  • ChrisGaffan
    ChrisGaffan Member

    @ColemenWilson Thank You.

Categories