Can you use Magic ETL to 'explode' table of transactions to all days?

M_Gonzalez
M_Gonzalez Member
edited August 2023 in Magic ETL

Table A has transactions that show an item's "Old Status" and "New Status" along with the date of the transaction. For example, row 1 shows that item XYZ changed from "Regular Price" to "Promo Price" on 1/1/2023. Row 2 shows that item XYZ changed from "Promo Price" to "Regular Price" on 1/4/2023. Row 3 shows that item XYZ changed from "Regular Price" to "Clearance" on 1/6/2023. etc

Table B has a list of all items

Using Magic ETL, how can I create Table C to include Date | Item | Status, showing 1 row per item per day for all days including the status listed from Table A

EDIT: The specific challenge is finding a technique that would allow me to identify the 'status' of each item, on each day, using the sparse transactional data from Table A

In the example above, I would expect the following results for item XYZ

1/1/23 | XYZ | Regular Price

1/2/23 | XYZ | Regular Price

1/3/23 | XYZ | Regular Price

1/4/23 | XYZ | Promo Price

1/5/23 | XYZ | Promo Price

1/6/23 | XYZ | Clearance

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @M_Gonzalez I apologize. After thinking about your situation more, I realized my last suggestion would not get you what you want. I did come up with a way to do it, though, and have created a video to walk you (and anyone else) through how to do it.

    https://youtu.be/MgB__mhgrU4

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MichelleH
    MichelleH Coach

    @M_Gonzalez The Calendar dataset from the Domo Dimensions Connector contains a list of all possible dates. I suggest joining Table A to the Calendar dataset then filtering to remove duplicate statuses

  • M_Gonzalez
    M_Gonzalez Member
    https://community-forums.domo.com/main/discussion/comment/75454#Comment_75454

    Thank you for your suggestion. I have updated my original question, which admittedly, was not detailed enough. 😀

  • MarkSnodgrass
    MarkSnodgrass Coach

    Here's a rough outline of how you would construct your ETL to do what you want.

    image.png

    Bring in the calendar dataset. I suggest filtering it a bit as it has about 30 years of dates in it. You can use a formula in your filter rule to make it dynamic and not hard-code dates in there.

    Get a distinct list of products. I used the group by tile to do this by putting the product name in the select section and then doing any sort of aggregation, such as count of products. You won't use the aggregation. It's just an easy way to get a distinct list.

    Add a constants tile to each with a field called JoinKey and value of 1 and then join the two together on that JoinKey column. This will give you a list of dates from the calendar for every product you have. For example, if I filtered my calendar to 2023, I would have 365 rows for Product A, 365 rows for Product B, etc…

    Do a Left Join that joins your product/calendar data to your main dataset and join on the date and the product.

    Add rank & window tile and use the lag function to get the previous status. Use an offset of 1 and and order by the date field from the calendar dataset and partition by product.

    Use the formula tile to do an IFNULL to replace blank statuses with the previous status.

    Hope this helps.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • M_Gonzalez
    M_Gonzalez Member

    @MarkSnodgrass , I knew you'd come through! I was just combing through your Youtube videos trying to see if you had mentioned something like this previously.

    For some reason, I never considered using a window function like LAG for a non numerical value.

    All of that said, I am still a bit lost with this point:
    "Use the formula tile to do an IFNULL to replace blank statuses with the previous status."
    Can you please elaborate?

    Thank you so much (for this, and all of the other contributions you make to the community)!

  • MarkSnodgrass
    MarkSnodgrass Coach

    @M_Gonzalez glad you are enjoying my videos. I think my "Filling in Date Gaps in Domo" would walk you through some of this.

    As far the the IFNULL, starting with your rank & window, the name you are assigning for your lag value is what you would be using in your IFNULL along with your main status column. With rank & window tile, you will be creating a new column and you might call it prev_status. Then, in your formula tile, you can fill in the blanks of your main status that exist by writing a formula like this IFNULL(status,prev_status) . This will look at the status column and if it is blank, it will populate it with the prev_status.

    I hope this works, but I haven't tested it out fully, but it might get you close to what you need.

    https://youtu.be/9iNpzSL5V6U

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @M_Gonzalez I apologize. After thinking about your situation more, I realized my last suggestion would not get you what you want. I did come up with a way to do it, though, and have created a video to walk you (and anyone else) through how to do it.

    https://youtu.be/MgB__mhgrU4

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • M_Gonzalez
    M_Gonzalez Member
    https://community-forums.domo.com/main/discussion/comment/75518#Comment_75518

    @MarkSnodgrass , you are THE man! I think I would have got there eventually using your "Filling in date gaps" video, but this latest one shows a considerably more efficient method than where I was headed. Thanks again!

Categories