topic Re: join load from files where exists a certain field in QlikView

join load from files where exists a certain field

Tue, 30 Jun 2015 19:09:21 GMT

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

Re: join load from files where exists a certain field

MK_QSL — Tue, 30 Jun 2015 19:40:29 GMT

Can you provide sample excel files also?

Re: join load from files where exists a certain field

sinanozdemir — Tue, 30 Jun 2015 19:55:46 GMT

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.

Re: join load from files where exists a certain field

Wed, 01 Jul 2015 04:25:13 GMT

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

Re: join load from files where exists a certain field

Wed, 01 Jul 2015 04:27:56 GMT

Hi Sinan,

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

Re: join load from files where exists a certain field

Wed, 01 Jul 2015 04:30:59 GMT

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

Re: join load from files where exists a certain field

Wed, 01 Jul 2015 19:59:08 GMT

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