topic Re: Load from QVD where values match range of values in App Development

Load from QVD where values match range of values

110 — Mon, 04 Sep 2023 12:50:59 GMT

I've tried searching the forums in vain - am looking to do similar to a SQL where + in clause , in which a query returns records with a value matching any of the values in the passed array, for example

Select * from PrimeNumbers where MyNumber in (1,2,3,5,7,13,17 )

I have a resident table, and a variable where I've stored a comma separated range of values in order to parameterise the query. Is this possible?

The 'in' keyword is not recongised.

[MyFilteredQVDTable]:

Load * from MyQVDTable where MyNumberKey in ( $( MyNumberKeyRange) )

Re: Load from QVD where values match range of values

marcus_sommer — Mon, 04 Sep 2023 12:53:47 GMT

You could apply it in this way:

Load * from MyQVDTable where match(MyNumberKey, $(MyNumberKeyRange));

Re: Load from QVD where values match range of values

Or — Mon, 04 Sep 2023 13:20:55 GMT

Just to add to @marcus_sommer solution, which is correct, if you're reading from a QVD you may go a slightly different direction. First, load the values you want to filter on into a table, e.g.

Load * INLINE [

MyNumberKey

5];

Then,

Load * from MyQVDTable where Exists(MyNumberKey);

This will result in an optimized QVD load.

Re: Load from QVD where values match range of values

110 — Mon, 04 Sep 2023 13:39:51 GMT

Thanks both @marcus_sommer and @Or - that's really helpful.

As it cuts out an additional step, I'll probably use the Exists () as I'm creating a concatenated variable from a table anyway.

Re: Load from QVD where values match range of values

Or — Mon, 04 Sep 2023 13:53:27 GMT

In that case, just make sure that the field name matches so you can get an optimized load (unless it's a small QVD, in which case optimization doesn't really matter).