1 Reply Latest reply: Sep 27, 2017 5:26 AM by Andrea Gigliotti RSS

    QV direct query

    Andrea Gigliotti

      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