Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Applicable88
Creator III
Creator III

Handling Null Values in a associated table

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. 

 

 

 

 

 

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

4 Replies
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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

2021_02_17_16_39_48_Mappe1_Excel.jpg

marcus_sommer

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