Jump to content

Empty attributes in the output table


Yi Lin

Recommended Posts

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?

Link to comment
Share on other sites

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. 

image.thumb.png.3faaad68074b82592d376cd663f20333.png


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). 
 

image.png.0ace1452958c83f347ef1eb8f683011c.png

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!

Link to comment
Share on other sites

  • 2 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...