Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikBeginner1
Creator
Creator

WHERE statement in data load editor

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 

QlikBeginner1_0-1603462755637.png

 

Labels (5)
1 Solution

Accepted Solutions
Kushal_Chawda

@QlikBeginner1  try below

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

View solution in original post

4 Replies
Kushal_Chawda

@QlikBeginner1  try below

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

View solution in original post

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

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