join load from files where exists a certain field

[#1]

Hi guys,

I am 'fighting' with QlikView  Skript and have a question. My problem is that I have different Excel Files, each containing different Fields (some are common, some not). What I want to do is to load everything only from files containing field 'Denial' with value 'Yes'. I have tried to do that with:

denial_article:

For Each file in filelist('pathtoFiles\*.xlsx')

let findfield = FieldNumber(Denial, '$(file)')

if $(findfield) > 0 then

join load * from file (ooxml, embedded labels, table is Data) where Denial = 'Yes';

endif;

next file;

but this does not seem to work...

Can you help me to find the way out?

Thanks in advance

[MK_QSL]

Can you provide sample excel files also?

[sinanozdemir]

Hi Ira,

Get rid of the "join" before "load" since you are doing an outer join, join clause is superfluous and it might be causing the undesired results.

[#4]

Hi, no I can not. But it looks like ->

OrderNr|Details|...........|Denial

787890| .......................|Yes

I have seen that I have forgotten to tell the name of the sheet 'Data', but it still does not work...

[#5]

Hi Sinan,

I will try it out today and will give you the answer later. Thank you for the answer.

[#6]

If you want to join data from different files, load a dummy table before For Each loop. Then join will work.

Table1:

Load 0 as Dummy

Autogenerate 1;

denial_article:

For Each file in filelist('pathtoFiles\*.xlsx')

let findfield = FieldNumber(Denial, '$(file)'); //Added semicolon

if $(findfield) > 0 then

Table_Temp:

join (Table1)

Load * from file where Denial = 'Yes';

endif //Removed semicolon

next file //Removed semicolon

Drop Field Dummy From Table1; //Drop the dummy field

Try this and let us know the result.

Regards,

KKR

[#7]

Hello Kranthikumar,

that does not seem to work.

I have solved it through:

table_temp:

For Each file in filelist('pathtoFiles\*.xlsx')

join Load *

from file;

next file

table_denial:

load *,

        0 as dummy

resident table_temp where Denial = 'Yes'

But still thank you for the answer