Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Supress null values in Pivot

Received Response
32
Views
3
Comments
Pawel X Dmochowski
Pawel X Dmochowski Rank 4 - Community Specialist
in Oracle Analytics Cloud and Server

Summary

Supress null values in Pivot

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.

Answers

  • Gabby Rubin-Oracle
    Gabby Rubin-Oracle Admin

    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

    Screen Shot 2018-03-01 at 4.20.06 PM.png

  • Gabby Rubin-Oracle
    Gabby Rubin-Oracle Admin

    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
    Pawel X Dmochowski Rank 4 - Community Specialist

    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:

    Capture1.PNG

    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.