Exists in LOAD of several tables using *

[#1]

Hi guys

I'm having a issue with something I'm doing and want to double check with you the right way to do it.

I have several order tables (2010 to 2015; files Orders_2010.qvd, Orders_2011.qvd...), the orders have CodDiagnostico and CodPrestacion fields. I must filter the orders by some CodDiagnostico and CodPrestacion previously loaded in inline tables (let's say I want orders with CodDiagnostico IN (5,3,2) and CodPrestacion IN (1,4,6) ).

What I'm doing is:

1. Loading inline tables that have the values I want to filter in full order table (concatenation of different years)

2. I have this LOAD sentence

Orders:

LOAD 

    IdAfiliado,

    [Fecha emisión]                as [Fecha cohorte],

    CodDiagnostico,

    CodPrestacion,

    Diagnostico,

    Prestación

FROM Orders_*.qvd (qvd)

WHERE Exists(CodDiagnostico)

OR Exists(CodPrestacion);

What I'm having back is almost the same result as concatenating all the orders years without doing any filter with the exists. Why I think is that maybe with the * the Exists isn't performed until all the years are loaded and thus almost all the CodDiagnostico and CodPrestacion does Exists and only a few are filtered.

Am I right? what's the best way to do it?, a LOAD changing CodDiagnostico and CodPrestacion names and then make a RESIDENT LOAD?

Thanks in advance

[jagan]

Hi,

Exists() will not work in this scenario, you need to use loop for this

//to read each file from the specified folder

FOR EACH file in FileList('filepath\*.xlsx')

LET vIsFirstFile = 1;

IF vIsFirstFile = 1 THEN
Table:
Load * ,
FileName() as File_Name
From $(file)(ooxml, embedded labels, table is [$Sheet1]);
ELSE

Concatenate(Table)
Load * ,
FileName() as File_Name
From $(file)(ooxml, embedded labels, table is [$Sheet1])

WHERE

Exists(CodDiagnostico)

OR Exists(CodPrestacion);

END IF
LET vIsFirstFile = vIsFirstFile  + 1;
Next

Hope this helps you.

Regards,

Jagan.