Unable to filter rows using WHERE while loading from a QVD

[TimeToQlik]

Spoiler

Hi, I'm new to Qlik Sense. I'm trying to modify an existing App in our Qlik Cloud environment. There is a load script that currently filters the rows if at least one of the two amount fields is not zero using a NOT MATCH, while selecting the data from a QVD. I would like to change the script to select the rows if sum of the two amount fields is greater than $100. I come from a database programming back ground and when I use the WHERE clause with the syntax Amount1 + Amount2 > 100, does not seem to work. Can someone help? Also, where can I find some help on this topic? I tried Qlik help, but couldn't find anything with basic search. Thanks in advance!


Table1:
LOAD
Field1,
Field2,
Amount1,
Amount2
FROM [lib://YourDataFolder/YourFile.qvd] (qvd)
WHERE Amount1 + Amount2 > 100;

[Chanty4u]

Try this 

Table1:

LOAD

    Field1,

    Field2,

    Amount1,

    Amount2

WHERE Amount1 + Amount2 > 100;

LOAD

    Field1,

    Field2,

    Amount1,

    Amount2

FROM [lib://YourDataFolder/YourFile.qvd] (qvd);

[QFabian]

Hi @TimeToQlik , it can be differents things :

Maybe Amount fields are not recognized as numbers, so you can force their interpretation. NUM#

Maybe you can put the add in parenthesis to secure the comparation against the sum of both fields ()

Table1:
LOAD
Field1,
Field2,
Amount1,
Amount2
FROM [lib://YourDataFolder/YourFile.qvd] (qvd)
WHERE (num#(Amount1) + num#(Amount2) ) > 100;

[rwunderlich · Accepted solution]

I would recommend using RangeSum() as your condition like this:

WHERE RangeSum(Amount1, Amount2) > 100;

Can you elaborate on what you mean by "does not seem to work"?  Not filtering, error message?

-Rob

[marksouzacosta]

Hi @TimeToQlik,

Can you please share your current real Load Script instead of a concept code?

Regards,

Mark Costa

[TimeToQlik]

I really appreciate you all for your quick response. I'm excited to be a part of this community already. 

I found the problem with my code.The issue seems to be with one of the amount fields being NULL. While I gave only two amount fields in my example, in my actual script I have 9 columns and one of them is calculated using a left join. This is causing the field to have NULL in some cases. I thought I handled the NULL using NullAsValue, but it looks like it does not work if the field is used as part of an expression. 

Here is the example code, much closer to my scenario:

// I have two tables Sales & Forecast

// The Sales table has Actual Sales and Sales Plan numbers by Product Group & Location

// The Forecast table has Forecast Numbers by Group only (but no Location)

// I need to allocate the Forecast numbers by Location using the Plan data

// Sales table by Group & Location

Sales:

Load * Inline [

ProductGroup, Location, ActualSalesAmt, SalesPlanAmt

Group1, Loc1, 100, 125

Group1, Loc2, 100, 125

Group2, Loc1, 50, 48

Group3, Loc1, 30, 25

Group3, Loc2, 40, 55

Group3, Loc3, 40, 45

];

// Forecast table by Group 

// Please note that there is no Forecast for Group2

Forecast:

Load * InLIne [

ProductGroup, SalesForecastAmt

Group1, 220

Group3, 120

];

NoConcatenate

// Join Sales 

// to sub-query, Fetch Sales Plan by Group, INNER JOIN

// to Forecast table to get the Forecast by Group, LEFT JOIN

Sales_WithForecast:

LOAD 

ProductGroup, 

    Location, 

    ActualSalesAmt,

    SalesPlanAmt AS SalesPlanAmt_ByLocation

Resident Sales;

INNER JOIN(Sales_WithForecast)

Load

ProductGroup,

SUM(SalesPlanAmt) AS SalesPlanAmt_ByGroup

Resident Sales

GROUP BY ProductGroup;

LEFT JOIN(Sales_WithForecast)

Load

ProductGroup,

SalesForecastAmt AS SalesForecastAmt_ByGroup

Resident Forecast;

Drop tables Sales, Forecast;

//Here is my attempt to handle NULLs, in case of Group2

NullAsValue SalesForecastAmt_ByGroup;

Set NullValue = 0;

NoConcatenate

Sales_Final:

LOAD

    ProductGroup, 

    Location, 

    ActualSalesAmt,

    SalesPlanAmt_ByLocation,

    // The NullAsValue worked when the field is by itself and set the following field to zero

    SalesForecastAmt_ByGroup,

    // Here is the expression to allocate the Forecast based on Plan

    // The NullAsValue did not work when the field 'SalesForecastAmt_ByGroup' is part of an expression

    // So, I had to use COALESCE function to fix the issue

    (SalesPlanAmt_ByLocation/SalesPlanAmt_ByGroup) 

    * COALESCE(SalesForecastAmt_ByGroup, 0) AS SalesForecastAmt_ByLocation

Resident Sales_WithForecast;

Drop table Sales_WithForecast;

Exit Script;

Using COALESCE fixed the issue, but please let me know if there is a more effective way to handle the issue or to handle NULLs in general. Like I mentioned, I'm new to Qlik and I'm open for any feedback. Thank you all very much once again!

[TimeToQlik]

Hello Rob, Thanks for your response! I should've been clearer. There was no error message, it was filtering the data incorrectly. 

I saw your solution after I handled the NULL using COALESCE explicitly, but using RangeSum would've worked too. I noticed that the funtion RangeSum replaces a expression/field with zero if it is NULL. 

[rwunderlich]

Yes, that's the advantage of RangeSum(). BTW, if you were going to use Coalesce() in this situation I would recommend using Alt() instead, as Alt is Coalesce for numbers. 

-Rob

[TimeToQlik]

Thanks again Rob, didn't know about Alt. I'll make the change.