2 Replies Latest reply: Jan 17, 2017 4:36 PM by Kenneth E Fannon RSS

    Having trouble displaying information in Text Objects when using Direct Query

    Kenneth E Fannon

      I have two QVW files, each with virtually the same information:

      • One pulls information from a SQL Database via a standard SELECT
      • The other pulls the same information from the same source, but via a DIRECT QUERY.

       

      I have a drill-down group (Department --> Machine) in both qvw files.

      When I select specific information to view, the table box reflects the information accurately for both qvw files.

       

      My problem is, on the qvw with the standard SELECT, I get the exact information I need in the text objects based on specific conditions.

       

      However, on the DIRECT QUERY qvw, some values display as expected, but others display as if the underlying field were null, when the Table Box clearly depicts the value.

       

      So my question is this:

       

      How to I duplicate the information that is accurately showing in the standard SELECT qvw, into the DIRECT QUERY qvw.  Scripts for each are below:

       

       

      SQL SELECT machineID,
      machineDesc,
      deptID,
      deptNum,
      deptDesc,
      colNum,
      colDesc,
      runTime,
      setupTime,
      downTime,
      noProdTime,
      totalTime,
      curSpeed,
      curLNFT,
      qtyPerHour,
      qtyProduced,
      qtyWasted,
      qtyProcessed,
      MTBF,
      downCount,
      shiftDesc,
      reportDT,
      processID,
      jobID,
      billToCustNum,
      billToCustomer,
      shipToCustNum,
      shipToCustomer,
      itemID,
      itemDesc,
      machineStateID,
      qtyLNFT,
      "pctAvailability_U",
      "pctAvailability_R",
      pctMaxSpeed,
      pctPerformance,
      pctQuality,
      pctWaste,
      "calcPctOEE_U",
      "calcPctOEE_R",
      "pctOEE_U",
      "pctOEE_R"
      FROM [Data Source Removed];

      ----

       

      DIRECT QUERY
      DIMENSION
      machineID
      ,machineDesc
      ,deptID
      ,deptDesc
      ,colNum
      ,colDesc
      MEASURE
      runTime
      ,setupTime
      ,downTime
      ,noProdTime
      ,totalTime
      ,curSpeed
      ,curLNFT
      ,qtyPerHour
      ,qtyProduced
      ,qtyLNFT
      ,qtyWasted
      ,qtyProcessed
      ,MTBF
      ,downCount
      ,shiftDesc
      ,reportDT
      ,jobID
      ,billToCustNum
      ,billToCustomer
      ,shipToCustNum
      ,shipToCustomer
      ,itemID
      ,itemDesc
      ,machineStateID
      ,
      [pctAvailability_U]
      ,
      [pctAvailability_R]
      ,pctPerformance
      ,pctQuality
      ,pctWaste
      ,
      [calcPctOEE_U]
      ,
      [calcPctOEE_R]
      ,
      [pctOEE_U]
      ,
      [pctOEE_R]
      FROM [Data Source Removed];

       

      Problems are primarily with my dimension fields, but I am experiencing issues also with a couple of measure fields (itemDesc is one culprit).

       

      If there is anything I can do to resolve this and make it work as expected, it would be greatly appreciated.

        • Re: Having trouble displaying information in Text Objects when using Direct Query
          Digvijay Singh

          Not sure but Ref manual has something on this as direct query uses source database to fetch measure fields,

          as it says -

          Differences between Data In-Memory and Database Data

          QlikView is case-sensitive when making associations with in-memory data. Direct Discovery selects data

          from source databases according to the case-sensitivity of the database. If a database is not case-sensitive, a

          Direct Discovery query might return data that an in-memory query would not. For example, if the following

          data exists in a database that is not case-sensitive, a Direct Discovery query of the value "Red" would return

          all four rows.

          an in-memory query may produce more matching values than a Direct Discovery query.

          The matches returned for Direct Discovery data depends on the database.

            • Re: Having trouble displaying information in Text Objects when using Direct Query
              Kenneth E Fannon

              Case sensitivity is completely irrelevant in this situation.

               

              You also missed the point of my question.  So to clarify:

               

              Given two separate QVW files, where the only difference is the querying method (in-memory vs direct discovery):

               

               

              When the selections on each ARE THE SAME (i.e. I'm making a selection on machineDesc and colDesc in EACH qvw), the values reflected in the table box are the same (one row in each table box), I can clearly see that itemDesc has a value in both QVWs.  The problem is that the TEXT OBJECTs associated with that particular field (text:  =itemDesc) are displaying different values.  On the in-memory QVW, I get the same value as reflected in the table box.  To my knowledge, this works as designed and is what I expect.  On the direct-query QVW, I get a NULL value in the text object, even though the table-box CLEARLY SHOWS a value.

               

               

              My question is why?  I'm getting one value from the in-memory.  I'm getting the exact same value from the direct-discovery.  Why are the text objects different?