Dimension information not showing when joining to a fact

[Anonymous]

Hi,

I am joining a dimension to a fact table.  When viewing the information in the front end there is dimension information missing for some of the records in the fact table.

For example:

ProductType               #Transactions

Beer                                   1000

Shoes                                      50

Toys                                        250

-                                             2000

There are 2000 transactions which does not have a ProductType asscociated with it.  Could I make the '-' , 'Unknown'.  What would be the best way to handle this problem?

Thank you                             

[Anonymous]

Hi Anto,

  It is because of outer join and data availability between the tables. So to put "Unknown" you can use

1)if condition in chart level

2) Apply map in script level.

Let me know still you need more clarification.

-Jai

[devarasu07]

Hi,

To fix your null value ('-') issue you have to update your dimension master table (i.e: product master it should contains all the fact product id desc. in this master table.)

and if you wish to update '-' as Unknown then u can write expression like below,

load *,
if(len(ProductType)<=0 or IsNull(ProductType) or ProductType='-','Unknown',ProductType) as [Product Revised];
LOAD * INLINE [
    ProductType,#Transactions
    Beer,1000
    Shoes,50
    Toys,250
    -,2000
];

[Anil_Babu_Samineni]

Try to interpret using Variable for that field

[Anonymous]

Hi Jai,

Could you give me an example of what the Apply map scripting would look like?

I tried the following with a mapping I did

ApplyMap('MapBranchToCustomer',Branch,null()) as BranchDescription

Thanks

[HirisH_V7]

Hi,

Check the both tables from business point of view,& determine what's the final view you wanna acheive.Accordingly use join or concatenate.

I think, that ProductType which got missed may have different type of transactions.

or

If at all , that's the final table means,use if condition.

Data:
 LOAD *,
 IF(ProductType='-','Unknown',ProductType) as ProductTypeNew
 
 INLINE [
 ProductType, #Transactions
 Beer, 1000
 Shoes, 50
 Toys, 250
 -, 2000
 
]; 

HTH,

Hirish