topic Looping for files in QlikView

Looping for files

Wed, 25 Nov 2020 16:16:04 GMT

Folder	FileName	FieldName
D:\Data\A	EMP_DATA_*.xlsx	Emp_code
D:\Data\A	EMP_DATA_*.xlsx	Emp_salary
D:\Data\B	SEGMENT_*.csv	Segment_id
D:\Data\B	SEGMENT_*.csv	Segment_name

Requirement :

Get the list of distinct Folder and loop through them. after scanning each folder(like D:\Data\A) get each files matching with FileName(like EMP_DATA_*.xlsx) and then match Fields of found Files with FieldName in above excel.

I'm stuck in loops which gives error again and again after many try.

Can any Qlik guy help here.

Thanks in Advance

Re: Looping for files

aarkay29 — Wed, 23 Aug 2017 14:54:46 GMT

Something like this

Folders:

Load

Folder,

FileName,

concat(FieldName,',',FieldName) as FieldName

Group by

Folder,

FileName;

LOAD

Folder,

FileName,

FieldName

FROM [

https://community.qlik.com/thread/272196](html, codepage is 1252, embedded labels, table is @1)

Where

SubField(FileName,'.',-1)='xlsx';

FOR i = 0 to NoOfRows('Folders')-1

LET vFolder = Peek('Folder',$(i), 'Folders');

LET vFileName = Peek('FileName',$(i), 'Folders');

LET vFieldName = Peek('FieldName',$(i), 'Folders');

FOR EACH file in FileList ('$(vFolder)\$(vFileName)');

load

$(vFieldName)

FROM

[$(file)] (ooxml, embedded labels, table is Sheet1);

Next file

Next i

I see two types of dataabses Use the code separately for .xlxs and .csv using where

Where

SubField(FileName,'.',-1)='xlsx';

As in the "from" statement attributes differ for .xlxs type and csv type

Re: Looping for files

Thu, 24 Aug 2017 09:51:00 GMT

Thanks for reply.

But how it will compare the fieldnames of foundfile to the expected fieldnames in above Fieldname column?

Re: Looping for files

aarkay29 — Thu, 24 Aug 2017 13:55:22 GMT

Can you explain a litle more what you are trying to achieve here ... with example may be better

Re: Looping for files

Thu, 24 Aug 2017 18:24:52 GMT

we have requirement like this :

compare fields of found files in Folders with field name in FieldName and if there is mismatch between them then needs to store this information of tablename and fields in .txt file.

For example:

At location D:\Data\A we have two files with there fields

1)EMP_DATA_1.xlsx ------------>Emp_code,Emp_salary

2)EMP_DATA_2.xlsx ------------>Emp_code,Emp_salry

So in above two tables and there fields we can see Emp_salry is mismatch with fields in FieldName of above excels.

Now we need to store information in .txt are as following

Mismatch Table : EMP_DATA_2.xlsx

Mismatch Field : Emp_salary

we need to have dynamic approach for all files at different Folder