Missing values in data model

[#1]

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.

[MK_QSL]

provide sample data please..

[#3]

For simplicity's sake:

Sales:

Customer	Origin	Dept	Sales
1	A	Clothing	790
1	B	Shoes	4,697

Dept:

Dept	Mall
Clothing	NY

[SunilChauhan]

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

[prieper]

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

[#6]

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]

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

[#8]

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.