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

[#1]

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

#####

[Anonymous]

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

[#3]

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 ?

[Anonymous]

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.

[#5]

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).

[Anonymous]

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

[maxgro]

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.