topic Re: WHERE statement in data load editor in App Development

WHERE statement in data load editor

QlikBeginner1 — Sat, 16 Nov 2024 17:55:37 GMT

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

Re: WHERE statement in data load editor

Kushal_Chawda — Fri, 23 Oct 2020 14:45:48 GMT

@QlikBeginner1 try below

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

Re: WHERE statement in data load editor

rwunderlich — Fri, 23 Oct 2020 18:14:49 GMT

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

Re: WHERE statement in data load editor

QlikBeginner1 — Fri, 23 Oct 2020 18:24:52 GMT

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!

Re: WHERE statement in data load editor

Kushal_Chawda — Fri, 23 Oct 2020 21:15:42 GMT

@QlikBeginner1 Please close this thread by accepting solution