QV direct query

agigliotti — Mon, 25 Sep 2017 13:41:17 GMT

Hello,

For our customer I'm using direct query to give them on demand data access.

In few words they get real time data at anytime.

direct discovery parameters:

SET DirectCacheSeconds= 300;

LET DirectStringQuoteChar=chr(39);

SET DirectTableBoxListThreshold= 1000;

SET DirectDistinctSupport='false';

SET DirectIdentifierQuoteStyle=’ANSI’;

SET DirectDateFormat='YYYY-MM-DD';

SET DirectUnicodeStrings = 'false';

SET DirectEnableSubquery = 'false';

DIRECT QUERY

DIMENSION

"ORDAG2" as "sales rep code",

"ORDNNO" as "order type"

MEASURE

(ORDIFT * ORDCRD * ORDCOE) as Amount

...

Zone:

LOAD

Mid(ATBKEY, 14, 2) as [sales rep code],

Mid(ATBUNI, 1, 20) as [Zone]

FROM ........

The data model is very simple with 2 tables: 1 in memory and 1 direct query.

i'm using a straight table with 1 dimension (Zone) e 1 measure (Amount).

below an example:

01 - zone A 1200,00

02 - zone B 3000,00

etc...

below Sales Amount expression:

Sum( aggr( if( [order type] = 'N', Sum(Amount)*-1,

if( [order type] <> 'N', Sum(Amount) ) ), Zone, [order type] ) )

inside the straight table object I get real time data only the first time I make a selection, the next times I get data from the cache (for Direct Discovery query results) even if time limit ( 300 seconds = 5 minutes) is reached.

While using a sample Sum(Amount) as straight table expression it works as expected (that is when time limit is reached Qlikview queries the source data for selections and recreates the cache for the designed time limit), but unfortunately I need to do an IF condition to perform the right calculation.

It seems you have to do a document reload to perform the query on database and to get data updated inside the straight table.

Is this a bug ? or is there another way to achieve what I need ?

Many thanks for your time in advance.

Best Regards

Andrea

Re: QV direct query

agigliotti — Wed, 27 Sep 2017 09:26:45 GMT

that was because DIMENSIONS are kept in memory.

I solved using a sample aggregation of the measure, as Sum(Amount), else I should do a document reload to see data updated.

topic QV direct query in QlikView

QV direct query

Re: QV direct query