topic Re: Loop through folder file and get Headers in QlikView

Loop through folder file

paulwalker — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

Hope you are doing all well!!

I have to loop data folder level, each folder has same structure of data different tables.. and header coming from separate table.

I have 2 folders for Jan and Feb, both has category and product table...

Scenario 1: I have to concatenate both Category tables and both Product tables - have to map Headers

Scenario 2: once done looping - have to create QVD(Category and product) and drop the tables.

I did something - individually working fine for me, but looping not working.

Header:
CrossTable(Field, FieldName, 2)
LOAD 'Dummy' as Dummy,
'@'&RowNo() as Key,
FieldName
FROM
[C:\Users\Lenovo\Desktop\Sample Data\Headers\Category.xlsx]
(ooxml, embedded labels, table is Sheet1);

MappingHeader:
Mapping Load Key, FieldName Resident Header;

DROP Table Header;
//===================================================
Sub ScanFolder(Root)

For Each File in '.txt'
For Each FileName in FileList(Root &'\*' & File)

Table:
LOAD *
// @1,
// @2
FROM
$(FileName)
//[C:\Users\Lenovo\Desktop\Sample Data\Files\Jan\Category.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

RENAME FIELDS USING MappingHeader;

Next FileName;
Next File;

For Each SubDirectory in DirList(Root &'\*')
CALL ScanFolder(SubDirectory)
NEXT SubDirectory

END SUB

Call ScanFolder('Path');

can you please help!!!

Re: Loop through folder file and get Headers

sunny_talwar — Tue, 22 Oct 2019 19:31:52 GMT

How about this?

Header: CrossTable(Field, FieldName, 2) LOAD 'Dummy' as Dummy, FileBaseName()&'.@'&RowNo() as Key, FieldName FROM [Headers\*.xlsx] (ooxml, embedded labels, table is Sheet1); MappingHeader: Mapping LOAD Key, FieldName Resident Header; DROP Table Header; //=================================================== Sub ScanFolder(Root) FOR Each File in '.txt' FOR Each FileName in FileList(Root &'\C*' & File) tmpCategory: LOAD * FROM $(FileName) (txt, codepage is 1252, no labels, delimiter is '\t', msq); NEXT FileName; NEXT File; FOR Each File in '.txt' FOR Each FileName in FileList(Root &'\P*' & File) tmpProduct: LOAD * FROM $(FileName) (txt, codepage is 1252, no labels, delimiter is '\t', msq); NEXT FileName; NEXT File; FOR Each SubDirectory in DirList(Root &'\*') CALL ScanFolder(SubDirectory) NEXT SubDirectory END SUB Call ScanFolder('Files'); QUALIFY *; Category: NoConcatenate LOAD * Resident tmpCategory; Product: NoConcatenate LOAD * Resident tmpProduct; DROP Tables tmpCategory, tmpProduct; UNQUALIFY *; RENAME FIELDS USING MappingHeader;

Re: Loop through folder file and get Headers

paulwalker — Wed, 23 Oct 2019 03:46:24 GMT

Thanks for Reply Sunny

Exactly this is what I need,

but problem is - actually my real data more than 20 tables in the folders, is there any way to do looping for the tables ??

is there any possibility ??

-Thanks

Re: Loop through folder file and get Headers

sunny_talwar — Wed, 23 Oct 2019 11:02:39 GMT

Try this

Header: CrossTable(Field, FieldName, 2) LOAD 'Dummy' as Dummy, FileBaseName()&'.@'&RowNo() as Key, FieldName FROM [Headers\*.xlsx] (ooxml, embedded labels, table is Sheet1); MappingHeader: Mapping LOAD Key, FieldName Resident Header; DROP Table Header; //=================================================== Sub ScanFolder(Root) FOR Each File in '.txt' FOR Each FileName in FileList(Root &'\*' & File) TRACE $(FileName); LET vTableName = 'tmp' & SubField(SubField('$(FileName)', '\', -1), '.', 1); TRACE $(vTableName); $(vTableName): LOAD * FROM $(FileName) (txt, codepage is 1252, no labels, delimiter is '\t', msq); NEXT FileName; NEXT File; FOR Each SubDirectory in DirList(Root &'\*') CALL ScanFolder(SubDirectory) NEXT SubDirectory END SUB Call ScanFolder('Files'); QUALIFY *; LET vTableCount = NoOfTables(); FOR i = 0 to $(vTableCount)-1 LET vTableName = TableName(0); LET vTableNameNew = Mid(TableName(0), 4); $(vTableNameNew): NoConcatenate LOAD * Resident $(vTableName); DROP Table $(vTableName); NEXT UNQUALIFY *; RENAME FIELDS USING MappingHeader;

Re: Loop through folder file and get Headers

paulwalker — Thu, 24 Oct 2019 02:47:43 GMT

Excellent Sunny! Thank You:)

Very Helpful to me!

If we have same field in both the tables, headers are not associating properly... thus have stored in QVD and Dropped the table.

Please see the below code highlighted in red..!

Header:
CrossTable(Field, FieldName, 2)
LOAD 'Dummy' as Dummy,
FileBaseName()&'.@'&RowNo() as Key,
FieldName
FROM [Headers\*.csv]
(ooxml, embedded labels, table is Sheet1);

MappingHeader:
Mapping
LOAD Key,
FieldName
Resident Header;

DROP Table Header;
//===================================================
Sub ScanFolder(Root)

FOR Each File in '.txt'

FOR Each FileName in FileList(Root &'\*' & File)

TRACE $(FileName);
LET vTableName = 'tmp' & SubField(SubField('$(FileName)', '\', -1), '.', 1);
TRACE $(vTableName);

$(vTableName):
LOAD *
FROM $(FileName)
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

NEXT FileName;

NEXT File;

FOR Each SubDirectory in DirList(Root &'\*')

CALL ScanFolder(SubDirectory)

NEXT SubDirectory

END SUB

Call ScanFolder('C:\Users\Lenovo\Desktop\Sample Data\Files');

QUALIFY *;

LET vTableCount = NoOfTables();

FOR i = 0 to $(vTableCount)-1

LET vTableName = TableName(0);
LET vTableNameNew = Mid(TableName(0), 4);

$(vTableNameNew):
NoConcatenate
LOAD *
Resident $(vTableName);

DROP Table $(vTableName);

RENAME FIELDS USING MappingHeader;

STORE $(vTableNameNew) into $(vTableNameNew).QVD(QVD);

DROP Table $(vTableNameNew);

UNQUALIFY *;

EXIT SCRIPT;

Re: Loop through folder file and get Headers

sunny_talwar — Thu, 24 Oct 2019 10:44:14 GMT

Amazing!! Thanks for sharing.

Re: Loop through folder file and get Headers

paulwalker — Thu, 24 Oct 2019 17:24:35 GMT

Hi Sunny,

facing one more problem..

Have added one more table, first loop is working fine.. (It has loaded 3 tables).

But, It's giving wrong tables count..

LET vTableCount = NoOfTables(); -- Here returns 2 tables, but I have 3 tables.

Please suggest.

Re: Loop through folder file and get Headers

sunny_talwar — Thu, 24 Oct 2019 18:32:51 GMT

Since the NoOfTable() is outside the loop, we should not see the value of vTableCount change, but the problem might be with LET vTableName = TableName(0); because by dropping the table you are changing the Tables and there indexes in your load script.

If you don't mind me asking, what issue did you run into when you did not store/drop the table?

Re: Loop through folder file and get Headers

paulwalker — Fri, 25 Oct 2019 03:12:25 GMT

below example, both tables has CategoryID field, Product table CategoryID storing Product.@1....

If we have duplicate fields also storing Product.@1, Product.@2..so on - Unique fields are storing fine..

and I have added 3 tables now, but 3rd tables not storing..

I did check with without Store/Drop as well..

NoOfTables() - The number of tables that were loaded, right ??
TableName('Index') - the name of the table based on its number in the list of tables

my understand, my table count returns wrong ??

PFA,

Re: Loop through folder file and get Headers

sunny_talwar — Fri, 25 Oct 2019 11:39:53 GMT

Yup, I see now what you mean. It seems like an issue which has been asked before as well Rename fields using mapping table . Anytime the FieldName repeats, the Rename command is not able to handle it

May be @hic can provide ideas.

Best,
Sunny

Re: Loop through folder file and get Headers

paulwalker — Fri, 25 Oct 2019 14:45:48 GMT

Oops.. Sunny another query...

Sorry for trouble you!!

Why it has concatenated both tables Product and Sales.. that should be synthetic key??

Both tables 2 fields are common and 3rd field is the different (Sales and Amount)- it should not be concatenate - right ????

Re: Loop through folder file and get Headers

sunny_talwar — Fri, 25 Oct 2019 14:45:45 GMT

Here you go

Header: CrossTable(Field, FieldName, 2) LOAD 'Dummy' as Dummy, FileBaseName()&'.@'&RowNo() as Key, FieldName FROM [Headers\*.csv] (ooxml, embedded labels, table is Sheet1); MappingHeader: Mapping LOAD Key, FieldName Resident Header; DROP Table Header; //=================================================== Sub ScanFolder(Root) FOR Each File in '.txt' FOR Each FileName in FileList(Root &'\*' & File) TRACE $(FileName); LET vTableName = 'tmp' & SubField(SubField('$(FileName)', '\', -1), '.', 1); TRACE $(vTableName); If NoOfRows('$(vTableName)') > 0 then Concatenate($(vTableName)) LOAD * FROM $(FileName) (txt, codepage is 1252, no labels, delimiter is '|', msq, header is 1 lines); ELSE $(vTableName): NoConcatenate LOAD * FROM $(FileName) (txt, codepage is 1252, no labels, delimiter is '|', msq, header is 1 lines); ENDIF; NEXT FileName; NEXT File; FOR Each SubDirectory in DirList(Root &'\*') CALL ScanFolder(SubDirectory) NEXT SubDirectory END SUB Call ScanFolder('Files'); QUALIFY *; LET vTableCount = NoOfTables(); TRACE $(vTableCount); FOR i = 0 to $(vTableCount)-1 TRACE $(i); LET vTableName = TableName(0); LET vTableNameNew = Mid(TableName(0), 4); $(vTableNameNew): NoConcatenate LOAD * Resident $(vTableName); DROP Table $(vTableName); NEXT UNQUALIFY *; RENAME FIELDS USING MappingHeader; //STORE $(vTableNameNew) into $(vTableNameNew).QVD(QVD); EXIT SCRIPT;

Re: Loop through folder file and get Headers

paulwalker — Fri, 25 Oct 2019 15:02:17 GMT

This is working Perfect!! and Rename field name issue also solved.

Thank You so much Sunny 🙂

Re: Loop through folder file and get Headers

sunny_talwar — Fri, 25 Oct 2019 15:39:16 GMT

How did you solve the rename field issue?

Re: Loop through folder file and get Headers

hic — Mon, 28 Oct 2019 09:02:19 GMT

"Rename Fields" cannot merge two loaded fields that are named differently.

To merge fields, you need to use an "Alias" statement before the Load. E.g.

Alias "x" as NewField;
Load x, ... From ... Table1;
Alias "y" as NewField;
Load y, ... From ... Table2;

The problem is to get the information from a mapping table into an Alias statement, but it can be done using variables and dollar expansions.

Re: Loop through folder file and get Headers

sunny_talwar — Mon, 28 Oct 2019 11:36:35 GMT

Thanks Henric!! I have never used Alias before, but will play around with the idea to see if I can figure this out.

Thank you,
Sunny