topic Re: Direct Discovery Query in QlikView

Direct Discovery Query

Anonymous — Wed, 04 Jul 2018 16:55:34 GMT

Hi All,

Please let me know how to code this in qlikview ,

CASE WHEN cs.alt_wrkr_id='Y'

THEN hw.alt_wrkr_id

ELSE 'XXXXXX' END AS alt_wrkr_id

the below sample code is working fine , but i need to add the above condtion in to the below code

DIRECT QUERY

DIMENSION

Table1.hcmcore_wrkr_key

FROM Table1.hcmcore_worker

Thanks in advance

Re: Direct Discovery Query

petter — Wed, 04 Jul 2018 18:54:03 GMT

You should be able to use the NATIVE() function that is supported in a DIRECT QUERY statement:

NATIVE data-source syntax can be used when the user knows the database to be queried and wants to exploit database-specific extensions to SQL. NATIVE data-source syntax is supported:

As field expressions in DIMENSION and MEASURE clauses
As the content of the WHERE clause

Probably like this:

NATIVE('CASE WHEN cs.alt_wrkr_id=''Y'' THEN hw.alt_wrkr_id ELSE ''XXXXXX'' END') AS alt_wrkr_id

NOTE: the '' above is two consecutive single quotes and not a double quote

NATIVE('CASE WHEN cs.alt_wrkr_id=' & Chr(39) & 'Y' & Chr(39) &' THEN hw.alt_wrkr_id ELSE ' & Chr(39) &'XXXXXX' & Chr(39) &' END') AS alt_wrkr_id

The last option is necessary in older versions of QlikView Prior to QlikView November 2017 I believe.

Re: Direct Discovery Query

Anonymous — Fri, 06 Jul 2018 07:25:47 GMT

Hi Peter,

Thanks for the reply , awesome

NATIVE('CASE WHEN cs.alt_wrkr_id=''Y'' THEN hw.alt_wrkr_id ELSE ''XXXXXX'' END') AS alt_wrkr_id

code worked like a charm

Re: Direct Discovery Query

petter — Fri, 06 Jul 2018 09:30:52 GMT

You're welcome

Re: Direct Discovery Query

Anonymous — Fri, 06 Jul 2018 11:52:29 GMT

Hi Peter ,

Is it possible to use QVD and Direct Query in a Qvw

example

Table1:

Load * from Qvd1.qvd(qvd);

Table2:

DIRECT QUERY

DIMENSION

Table1.hcmcore_wrkr_key

FROM Table1.hcmcore_worker

Thanks in advance