"IN" clause based on an MSExcel file load

[Anonymous]

We have an MS Excel file that we load via Qlikview script editor. The Excel file has one column and let's say 1,000 rows.  This one column stores one unique Invoice ID in each row.

We also have a SQL query in script editor. We need the sql query to run and return the list of fields in the query only for Invoice ID's that are in the MS Excel file.

I know we can do an IN clause in the query and paste in these 1,000 Invoice ID's into the IN clause, but we're hoping there is a more direct way to apply the filter for the sql query, based on whatever Invoice ID's exists in the MS Excel file.

Here's what we have now, the results set is the entire list combined from both the sql query and the load file, what we want is only rows returned by the SQL that have Invoice ID's existing in the MS Excel file.

We're not sure if we should use a join condition or an IN clause or what the best way to do so is.

Please let me know if this is possible and the preferred method to so do.

Thanks,

Jon

SELECT
A.O_CODE,
A.G_UNIT,
SUM(A.X_AMOUNT) as AMT, A.T_OM, A.R_NO, A.INVOICE, A.T_DESCR, A.U_DESCR, C.NAME1, C.ID, C.T_ID
FROM (P_SPLT A LEFT OUTER JOIN  I_HDR B ON  B.INVOICE = A.INVOICE ), CUSTOMER C
WHERE (A.E_DT IS NULL AND C.CUST_ID = B.O_CUST_ID)

LOAD o_CODE
FROM
(biff, embedded labels, table is Sheet1$);

[el_aprendiz111]

Hi,

1 option:

qry : LOAD o_CODE FROM (biff, embedded labels, table is Sheet1$);

CDs:  LOAD Concat(o_CODE,',') AS o_CODE Resident qry;

LET vIN_CDs = trim(Peek(' o_CODE '));
DROP Table qry,CDs;

SQL SELECT * FROM TBL WHERE ID IN($(vIN_CDs));

[boorgura]

You can use this to mimic IN function.

File:

LOAD o_CODE
FROM
(biff, embedded labels, table is Sheet1$);

DB:

LOAD *

where exists(O_CODE, OCODE);

SELECT
A.O_CODE OCODE,
A.G_UNIT,
SUM(A.X_AMOUNT) as AMT, A.T_OM, A.R_NO, A.INVOICE, A.T_DESCR, A.U_DESCR, C.NAME1, C.ID, C.T_ID
FROM (P_SPLT A LEFT OUTER JOIN  I_HDR B ON  B.INVOICE = A.INVOICE ), CUSTOMER C
WHERE (A.E_DT IS NULL AND C.CUST_ID = B.O_CUST_ID)

Not mandatory to rename the field the second load. Just helps for debugging purposes.

[crusader_]

Good option,

But be aware of IN clause limitations - check your DB documentation.

Oracle, for instance, has MAX 1000 elements limit in IN CLAUSE.

//Andrei

[marcus_sommer]

Beside the IN logic and the similar Qlik methods of match() and exists() an INNER join would do the same and if you could load the excel into the database you could apply the join there which would avoid that at first all records are transferred to Qlik and the filter is there applied on the whole dataset.

By rather small datasets it won't give much benefit but by large amounts of data respectively if the responses from the database and/or the network are rather slow it will make a significantely difference.

- Marcus