Yi Lin Posted December 19, 2023 Share Posted December 19, 2023 Hello community! I have some weekly data from the production processes of various resources. It's possible that specific weeks are absent in the dataset for certain resources. Despite this, I must ensure that all weeks, even if empty, are represented in the output table for each resource. Any clue on how to achieve this? Want to know more? Link to comment Share on other sites More sharing options...
Mike Harris Posted December 21, 2023 Share Posted December 21, 2023 Hi! This is a possible way to get what you need: 1. Create a table with the list of weeks: • You can start with an empty source and fill it using the enum() function. • Alternatively, you can start with the main table and group by week, assuming that all the necessary weeks are present at least once in the dataset. 2. Create a table listing the resources. This can be done by grouping by the resource in the main table. 3. Join the two tables above using a Cartesian product, as shown below. The result will be a table where you have all the possible combinations of week/resource (in the example below, the weeks range from 1 to 10, but this will depend on your specific use case). 4. Join the output of the above join with the main table using a left outer join. The output of this join should be the table you were expecting. Hope it helps! Want to know more? Link to comment Share on other sites More sharing options...
Griz Posted January 3 Share Posted January 3 How do you create a table with the list of weeks using the enum() function. Want to know more? Link to comment Share on other sites More sharing options...
Mike Harris Posted January 4 Share Posted January 4 (edited) I've just inserted in a DM the number rows one for each week that I needed and after it I filled the column "Week" using the function enum() Edited January 4 by Mike Harris Want to know more? Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.