Empty attributes in the output table

[Yi Lin]

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?

[Mike Harris]

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!

[Griz]

How do you create a table with the list of weeks using the enum() function.

[Mike Harris]

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