topic Trying to replicate the "WHERE IN" function using QVD's in QlikView

Trying to replicate the "WHERE IN" function using QVD's

Tue, 15 Sep 2015 20:18:09 GMT

I am trying to replicated "WHERE IN" from SQL Select but this time using QVD's .Let me know if am doing wrong.. any help is appreciated..

LOAD distinct
1 as CT_EMPLOYEE,
// EMPLID AS EMPLID_HIST,
EMPLID,
// USERTYPE,
NAME
// ApplyMap('JOBSH2LGCOUNTRY',UH_COUNTRY) as COUNTRY
FROM $(vQVDDirectory)\CORE_USERS_HISTORY.qvd (qvd)
WHERE EMPLID = (SELECT EMPLID FROM $(vQVDDirectory)\USERS.qvd (qvd) WHERE C_COUNTRY = 'SGP' and USERTYPE = 'E');

I have an error "

####

Garbage after statement

INNER JOIN

LOAD distinct

#####

Re: Trying to replicate the "WHERE IN" function using QVD's

Anonymous — Wed, 16 Sep 2015 22:05:47 GMT

Hi there. WHERE IN clauses are really useful in SQL. One of the best ways to do it uses the EXISTS statement and performs really quickly. To do it you need to load your USERS.qvd first. Here is the approach:

//== LOAD LIST OF EMPLIDs ===

Temp_table_01:

LOAD

EMPLID

FROM $(vQVDDirectory)\USERS.qvd (qvd)

;

//== LOAD PRIMARY DATA ===

USER_HISTORY:

LOAD DISTINCT

1 as CT_EMPLOYEE,

// EMPLID AS EMPLID_HIST,

EMPLID,

// USERTYPE,

NAME

// ApplyMap('JOBSH2LGCOUNTRY',UH_COUNTRY) as COUNTRY

FROM $(vQVDDirectory)\CORE_USERS_HISTORY.qvd (qvd)

WHERE EXISTS(EMPLID)

;

DROP TABLE Temp_table_01;

Notes:

1. In the first LOAD statement I did not use a DISTINCT keyword - adding a DISTINCT would force it to load 'non-optimized' - much slower.... and it has no negative impact on the subsequent EXISTS statement

2. The EXISTS statement usually allows the LOAD to proceed using the 'optimized' (fastest) method. As opposed to using a JOIN or APPLYMAP which will slow down the load. Using EXISTS isn't always the fastest approach, in certain situations APPLYMAP can be faster overall. Usually a join is the slowest.

Hope this helps!!

John

Re: Trying to replicate the "WHERE IN" function using QVD's

Wed, 16 Sep 2015 22:17:57 GMT

Thanks for your help John..

forgive my ignorance..It might be a dump question.. trying to understand the binary reference here

How does the USER_HISTORY will know to reference Temp_table_01 EMPLID? because the user_history also has emplid ...

Hope I am making sense ?

Re: Trying to replicate the "WHERE IN" function using QVD's

Anonymous — Wed, 16 Sep 2015 22:30:13 GMT

Thats a great question. The EXISTS statement looks across all fields loaded thus far, finds other previously load fields with the same exact field name and searches their contents. Unlike a relational database it uses Qlikview's associative model that assumes that any two or more identically named fields should contain the same type of content.

Also, if the field in the table you are loading and seeking to filter using EXISTS is named differently - even if you are renaming it using 'AS' you need to complete the exists statement like this:

LOAD

SpecialDate as Date

FROM XYZ.qvd (qvd)

WHERE EXISTS(Date,SpecialDate)

In this example, 'Date' has been loaded previous to this load and you are bringing in 'SpecialDate' and renaming it 'Date'... The EXISTS function executes prior to the renaming of the field.

Re: Trying to replicate the "WHERE IN" function using QVD's

Wed, 16 Sep 2015 23:12:24 GMT

Thanks for the time taken to explain it. My understanding is. If the field name matches from the previous table the EXISTS function reference the previous table loaded table name(s).

Re: Trying to replicate the "WHERE IN" function using QVD's

Anonymous — Thu, 17 Sep 2015 15:52:51 GMT

Correct. If a field has been loaded it will be searched by the Exists() function.

Re: Trying to replicate the "WHERE IN" function using QVD's

maxgro — Thu, 17 Sep 2015 22:17:42 GMT

How does the USER_HISTORY will know to reference Temp_table_01 EMPLID? because the user_history also has emplid ...

It doesn't need to know the table.

It references the field EMPLID.