Problem with Crosstable of Multiple excel files

[#1]

Hi,

i'm loading in script all the files of a folder. They are crosstables in excel with the same structure, and all the data is loading correctly also using transpose, but i can't distinguish which data is of each month.

The only way to get the month is with the 2 first characters of file name, eg: 01_Data.xls where 01 = january, but i can't do it in the CrossTable.

I attach a zip with a example, and the code is:

// Set ErrorMode =0 QlikView will simply ignore the failure and continue script execution
// This is done because in some file some sheet may be missing
set ErrorMode=0;

for vYear=2011 to $(vYearEnd)
 for tMonth = 1 to 12
 let vMonth = right('00' & '$(tMonth)',2);
 let vPath = '[.' & $(vYear) & '' & $(vMonth) & '_Data.xls]';

 CrossTable(Type, Actions)
 LOAD ACTIONS_D as Employee,
 ACTION1,
 ACTION2,
 ACTION3
 FROM
 $(vPath)
 (biff, embedded labels, table is DATA$, filters(
 Remove(Col, Pos(Top, 2)),
 Remove(Row, Pos(Top, 12)),
 Remove(Row, Pos(Top, 11)),
 Remove(Row, Pos(Top, 10)),
 Remove(Row, Pos(Top, 9)),
 Remove(Row, Pos(Top, 8)),
 Remove(Row, Pos(Top, 7)),
 Remove(Row, Pos(Top, 6)),
 Remove(Row, Pos(Top, 5)),
 Transpose()
 ));
 next
next ;

// Return error mode to default value of 1 - script execution will halt and the user will be prompted for action
set ErrorMode=1;

Hope you can help me

Thanks

[#2 · Accepted solution]

What about this:

// Set ErrorMode =0 QlikView will simply ignore the failure and continue script execution
// This is done because in some file some sheet may be missing
set ErrorMode=0;

for vYear=2011 to $(vYearEnd)
 for tMonth = 1 to 12
 let vMonth = right('00' & '$(tMonth)',2);
 let vPath = '[.\' & $(vYear) & '\' & $(vMonth) & '_Data.xls]';

 CrossTable(Type, Actions,2)
 LOAD ACTIONS_D as Employee,
 //$(tMonth) as 'Month', this might work also but I would use the line below
 left(filename(),2) as 'Month',
 ACTION1,
 ACTION2,
 ACTION3
 FROM
 $(vPath)
 (biff, embedded labels, table is DATA$, filters(
 Remove(Col, Pos(Top, 2)),
 Remove(Row, Pos(Top, 12)),
 Remove(Row, Pos(Top, 11)),
 Remove(Row, Pos(Top, 10)),
 Remove(Row, Pos(Top, 9)),
 Remove(Row, Pos(Top, 8)),
 Remove(Row, Pos(Top, 7)),
 Remove(Row, Pos(Top, 6)),
 Remove(Row, Pos(Top, 5)),
 Transpose()
 ));
 next
next ;

// Return error mode to default value of 1 - script execution will halt and the user will be prompted for action
set ErrorMode=1;

[#3]

That is Trent, thank you very much!

[#4]

Just FYI there is also a filetime() function that works the same way as filename() which is useful if say you have 2 accounts with 01_ in that folder and you want the file that was updated most recently.

[#5]

this time no files are similar but it is good advice for future, thanks.