Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
agigliotti
Partner - Champion
Partner - Champion

QV direct query

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

1 Solution

Accepted Solutions
agigliotti
Partner - Champion
Partner - Champion
Author

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.

View solution in original post

1 Reply
agigliotti
Partner - Champion
Partner - Champion
Author

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.