Direct Discovery Query

[Anonymous]

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

[petter · Accepted solution]

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

OR

       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.

[Anonymous]

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

[petter]

You're welcome

[Anonymous]

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