Handling Null Values in a associated table

[Applicable88]

Hello,

i have several tables linked ONLY via the data manager. They are not linked via a pre DataWareHouse nor in the Data Load Editor.

There are two tables associated via the ordernumbers. Table1 consist all orders and Table2 only when already ready shipped with an outbounddate. If there is no outbounddate apparently the ordernumber not existing in Table2. 

I have trouble using set analysis to exlude all orders which already have a outbounddate. I got the tip from @marcus_sommer to load it as NULL, so its practically not empy and i can exlude all values with NULL in it. 

I tried this to set up with two possiblities, one with the alt()-function and another with a classical if-statement to cover three possibliites like zero values, null and len(trim)) -function in the load editor:

[Table1]:

LOAD

Ordernumber
YEAR,
Active
Startdate

from......

[Table2]:
LOAD
Ordernumber,
Date  as Outbounddate,
if(alt(Outbounddate,'1'),1,0) as Flag,
if(ISNULL(Outbounddate) OR LEN(trim(Outbounddate))=0 OR Outbounddate=0,0,1) as OutbounddateFlag
from...

I still don't get the null values displayed. Is it really necessary to "link" them via load editor to filter out the nulls?

Thank you in advance.

Best. 

[marcus_sommer]

I would say your challenge are not NULL's else missing data. Without knowing all your data and all requirements it's not really possible to give more as a few hints.

Personally I would tend to try to concatenate all fact-tables into a single fact-table to get a star-scheme. It avoids a lot of trouble to associate them with the dimension-tables without getting synthetic keys or circular loops or the problems which comes by doubling/renaming multiple fields/tables. Also the quite common fact that not all key-values are available on each side. Of course everything of the potential challenges could be solved with other methods but won't be easier.

In your case it wouldn't be enough because the missing records are further missing - you may generate them within an extra step. If there are not much more of such specific requirements I would start a bit simpler by generating an appropriate flag-field. This may look like:

ForExists: load Ordernumber from Table2;

[Table1+2]: load *, exists(Ordernumber) as Flag from Table1;
concatenate([Table1+2]) load * from Table2;

Afterwards you could use: ... Flag = {0} ... within the set analysis. 

- Marcus

[Applicable88]

Hello @marcus_sommer 

the two tables basically looks like the jpeg above. As you see Table no1 consist every order and table2 only thos

who already are tracked having and outbound date.When I associate them via data load manager as two bubbles, I

cannot count or flag the empty ones in table2 as null and filter them out in table1. I try to concatenate them as you said, to see if it works out. I will let you know. thank you very much so far. 

Best. 

[Applicable88]

[marcus_sommer · Accepted solution]

I don't know if it's possible to define this kind of transformation within the data-load manager which is probably not suitable for advanced transformations . If it's not possible there you could switch to the script to add/adjust the autogenerated script.

- Marcus