WHERE statement in data load editor

[QlikBeginner1]

Hello

This may be a simple solution, however I have been trying to get this to work but unable to.

I am creating a reconciliation app, and trying to limit the load based off a date field.

The date field is 'TransactionEffectiveDate'. In the back end I use this:

FROM [lib://QVD/DTL\TransactionEntry.QVD] (qvd)
where TransactionEntrySourceSystemName = 'COGEN' or TransactionEntrySourceSystemName = 'Acturis BE' and date(floor(TransactionEffectiveDate)) >= '01-01-2019'; however, it still loads all dates?

How can I restrict this so that the app only loads where the TransactionEffectiveDate is greater then 01/01/2019? so not loading any dates previous to that date? Thanks in advance 

[Kushal_Chawda · Accepted solution]

@QlikBeginner1  try below

where match(TransactionEntrySourceSystemName , 'COGEN' ,'Acturis BE' ) and floor(TransactionEffectiveDate)>= floor(makedate(2019,1,1))

[rwunderlich]

The where clause is evaluated using the rules of boolean algebra. The order of operations for Boolean algebra, from highest to lowest priority is NOT, then AND, then OR. Expressions inside brackets are always evaluated first. 

In your case, if TransactionEntrySourceSystemName = 'COGEN'  is true, the record will be included regardless of the date.  If you write out your expression like this:

true OR false AND false

You can see that evaluating AND first results in your entire expression being true.  You can clarify your intent by surrounding the the TransactionEntrySourceSystemName tests with parens:

(true OR false)  AND false

or using the match() function as @Kushal_Chawda suggested, effectively combining the two.  

-Rob

[QlikBeginner1]

Both, Thank you so much for your input.

@Kushal_Chawda - this is perfect and did exactly what i needed to do.

@rwunderlich - thank you for explaining this to me, makes much more sense.

Thank you all!

[Kushal_Chawda]

@QlikBeginner1  Please close this thread by accepting solution