Supress null values in Pivot

Question

Summary

Content

Hi,

I have a data set with names and reported hours against each week.

So in my rows I have the names, in columns I have weeks, and for values I have hours.

The issues I'm trying to solve is that if a person didn't report in a given week range - his line disappears from the pivot.

What I need is keep the person and display the nulls or zeros...

Any ideas how to achieve this?

Best regards,

Pawel.

Gabby Rubin-Oracle · Answer

Hi Pawel,

If you have zeros or nulls for the employee in that week, it should work. The issue is if you do not have any record of that employee in that week range (no null or zero). In this case, the range of the query will not have any row with that employee so the rows will not be shown. You have to have at least an empty row for that employee in order for it to be shown. The screenshot below shows Alan that has 0 hours, Frank that has Null hours, and Smith that did not have any record for the first 6 weeks.

Regards,

Gabby

Gabby Rubin-Oracle · Answer

Hi Pawel,

What you need is to create a cartesian product between the two table, so it is basically a no join situation (outer join does not help here). The only way I can think of doing it is to create a SQL based data set that selects the relevant data from both tables without a join. Once you have done that, you will need to clean the unnecessary repeated data that will be generated.

Regards,

Gabby

Pawel X Dmochowski · Answer

Hi Gabby,

Thanks for Your explanation. In this case, is there a way to add nulls during data flow?

In am joining all my data from one place with another excel that has a listing all week end dates:

So, I end up with some empty rows with only the week end date in the last column.

Not sure if these are nulls in this case or simply "empty" rows?

Regards,

Pawel.