Show 0 in Pivot Table

[StacyCui]

Dear

I have a order table which has sales district info , The below 'RC_PSS Simogear'  shows 0  in PY in the pivot chart. How to  create like it?

This is my pivot chart. It could not show the RC_PSS Simogear.  Should I use "Outer Join"?  Use Order table outer join Master Calendar table? or is there any other method?  I am really appreciated for your reply.

[Mark_Little]

Hi

If you are joining the Dimension to your facts then if there is no data it would drop it off, Just changing it to Join should resolve it.

Make sure you also have Include Null value on the dimension ticked. It will show as Null '-' 

[StacyCui]

Thank for your reply. I did not join a dimension table to order table.  The order table has a  district field  as the below .

So you mean that I need use district table (dimension table )to join order table and it will work? Here is the script:

I am not sure It is right?

LOAD ID,
date("FROZENDATE")as "FROZENDATE",
"AKZ",
"BU",
"BUSEGMENT",
"GCK",
"ORVALUE",
"ORDERSOURCE",
"ORTYPE",
"COMPANYCNOC",
"COMPANYSAPNO",
"BUYERCN",
"BUYERCNOC",
"BUYERSAPNO",
"SALESNAME",
"SALESTERRITORY",
"SALESBRANCH",
"SALESOFFICE",
"SALESDEPARTMENT",
"SALESDISTRICT",
"SALESTYPE1",
"SALESTYPE2",
"ASSIGNVALID",
"ECODE";
SQL SELECT "ID",
"FROZENDATE",
"AKZ",
"BU",
"BUSEGMENT",
"GCK",
"ORVALUE",
"ORDERSOURCE",
"CONTROLLINGAREA",
"SELLINGPARTY",
"ORTYPE",
"CONTRACTNO",
"COMPANYNAMECN",
"COMPANYNAMEEN",
"COMPANYCNOC",
"COMPANYSAPNO",
"IFA",
"BUYERCN",
"BUYEREN",
"BUYERCNOC",
"BUYERSAPNO",
"VBEZ",
"SALESNAME",
"USERGID",
"EMAIL",
"SALESTYPE1",
"SALESTYPE2",
"SALESTERRITORY",
"SALESBRANCH",
"SALESOFFICE",
"SALESDEPARTMENT",
"SALESDISTRICT",
"SPOREFNO",
"SPRNO",
"ASSIGNVALID",
"ECODE",
"VBEZFOCUSBRANCH"
FROM   order table;

Outer join//---------------------------????

LOAD
"SALESTERRITORY",
"SALESBRANCHID",
"SALESBRANCH",
"SALESOFFICE",
"SALESDEPARTMENT",
"SALESDISTRICT";
SQL SELECT "ID",
"LEADCOUNTRYID",
"LEADCOUNTRY",
"SALESCOUNTRYID",
"SALESCOUNTRY",
"SALESSUBREGIONID",
"SALESSUBREGION",
"SALESTERRITORYID",
"SALESTERRITORY",
"SALESBRANCHID",
"SALESBRANCH",
"SALESOFFICEID",
"SALESOFFICE",
"SALESDEPARTMENTID",
"SALESDEPARTMENT",
"SALESDISTRICTID",
"SALESDISTRICT";
"ISVALID",
"VBEZFOCUSBRANCH"
FROM  Territory Structure;

[Mark_Little]

HI

What happens when you run the above script with the outer join?