topic Having trouble displaying information in Text Objects when using Direct Query in QlikView

Having trouble displaying information in Text Objects when using Direct Query

Tue, 17 Jan 2017 18:41:19 GMT

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 — Tue, 17 Jan 2017 19:22:19 GMT

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

Tue, 17 Jan 2017 21:36:22 GMT

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?