Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Missing values in data model

Hi,

I have a data model built in the following manner:

I've encountered a problem due to the fact that not all Dept. values in 'Sales' have a corresponding value in 'Dept.', for example: there's no entry for department 'Shoes' in table 'Dept', although 'Shoes' exists in table 'Sales'. This creates a problem when using an expression like:

sum( { 1 <Mall=P()> } Sales)

Departments with no 'Mall' are not counted in the sum..in general, how can one work around this problem? I tried using NULLASVALUE but it didn't help. Only having a value for each department in both tables solved this.

Thanks in advance.

7 Replies
MK_QSL
MVP
MVP

provide sample data please..

Not applicable
Author

For simplicity's sake:

Sales:

 

CustomerOriginDeptSales
1AClothing790
1BShoes4,697

Dept:

 

DeptMall
ClothingNY
IamBack
Champion
Champion

try this

Sales:

Load * inline [

Customer, Origin ,Dept, Sales

1, A, Clothing, 790

1, B, Shoes, '4,697'

];

Dept:

left Join(Sales)

Load * inline [

Dept, Mall

Clothing, NY

];

Load Customer, Origin ,Dept, Sales,

if(len(Mall)=0,'Blank',Mall) as Mall,

1 as test

Resident Sales;

drop table Sales;

see the attched file

Sunil Chauhan
prieper
Master II
Master II

If you wish to keep your datamodel, you may wish to work with the OTHERSYMBOL-functionality:

SET OTHERSYMBOL = +;

Sales:

LOAD * INLINE [ Customer, Origin, Dept, Sales

1, A, Clothing, 790

1, B, Shoes, 4697];

Dept:

LOAD * INLINE [Dept, Mall

Clothing, NY];

MissingDept:

LOAD * INLINE [Dept, Mall

+, #N/A];

Doing so, will allow you to select all Dept, where there is no corresponding entry (have choosen #N/A, but may use any other text)

HTH Peter

Not applicable
Author

Thanks to both of you, but I've provided an example where only one field is missing values. In reality, I may have situations where multiple dimensions are missing values, hence going about one of the solutions provided above seems a bit cumbersome.

Since I'm using the expression sum( { 1 <Mall=P()> } Sales), I was wandering if there was a workaround involving the expression itself perhaps, which would take into account stores which are not associated with any Malls (nulls..). As you can see currently, they are simply ignored.

prieper
Master II
Master II

Yoni,

generally think that

a) during the development you should have a clear imagination, which data you may expect in your dimensions

b) it is a good practice to replace all missing values (as above, APPLYMAP() seem to have a better performance than IF-constructions.

HTH Peter

Not applicable
Author

I believe I've found the solution for this - it's in this extremely helpful video:

https://www.youtube.com/watch?v=pGpdfMpmWoE

Just wanted to share.