Remove null values from table by changing the script

[Elkhan]

Hi, 

Could you please help me? I removed the customer number in the script by using "where not function": 

[Sell-to Customer] where not([Sell-to Customer]= 'XXX') 

Now, I can see that this customer is no longer on my table. However, I still can see orders related to this customer as part of my dimensions and qty as part of my measure. 

I am trying to solve it in load editor. Could you please advise? 

Thank you in advance, 

Regards,

Elkhan  

[stevedark]

Hi @Elkhan 

My guess as to what has happened here is that you have separate tables with your customers in as to your orders? You have removed the customer from the customer table, but their orders remain.

In the table with the orders, what field links to the table with the customers? This can be used to limit the order table.

To do this, make sure you load the customer table first, with the WHERE code you have above. Then (assuming a link on CustomerID) load the orders with a WHERE EXISTS on the sell to CustomerID, like this:

LOAD
  CustomerID,
  [Order Number],
  [Quantity To Ship]
FROM [lib://data/MyOrders.qvd] (qvd)
WHERE NOT EXISTS (CustomerID);

Hope that helps.

Steve

[Elkhan]

Hi @stevedark , 

Thank you very much for your reply. I have two order tables from Navision Sales order and Sales Blanket Orders. The problem is in the second table I am not using Customer ID at all. I just took Order Number to link it to the first table and qty. 

Do you mean that I need also load Customer ID from the second table and then exclude it with "where not" function? 

Thank you in advance, 

Regards,

Elkhan 

[stevedark]

Hi @Elkhan 

You may be able to do it with:

WHERE NOT EXISTS ([Order Number])

Provided that this is the field that joins the two tables together.

You will not be able to do this in the SQL query to Navision (as it is Qlik syntax) but you could do it in a preceding load, e.g.

LOAD
   *
   WHERE NOT EXISTS ([Order Number])
   ;
SQL SELECT
   etc.

The thing with this is that all of the data comes back from SQL and then you ditch chunks of it in Qlik. You will be better to limit the data using a SQL WHERE statement, before it even gets to Qlik.

To limit to the Sales Blanket Orders you could do an INNER JOIN to the Sales Order table, with the WHERE statement on it. Just make sure you have the correct indexes in SQL to make the query work well.

Hope that makes sense?

Steve