Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

join load from files where exists a certain field

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

6 Replies
MVP
MVP

Re: join load from files where exists a certain field

Can you provide sample excel files also?

sinanozdemir
Valued Contributor III

Re: join load from files where exists a certain field

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.

Not applicable

Re: join load from files where exists a certain field

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

Not applicable

Re: join load from files where exists a certain field

Hi Sinan,

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

Not applicable

Re: join load from files where exists a certain field

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

Not applicable

Re: join load from files where exists a certain field

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


Community Browser