Read cell value and append it to an additional column?

[#1]

Hello everyone,

I have encountered a situation here:

There are over 40 excel files with similar names (CompanyX 07 2014.xlsx) in a folder, and I need to load all of them .

The header are on line 7 but I have to read a cell value (B2, it's the name of the company) and add it as the value of a auto generated column.

How can I achieve it ?

Thank you a lot!

[MarcoWedel]

Hi,

define the source filenames with wildcards like "*.xlsx".

Furthermore you might read your company names from the filename also:

tabData:
LOAD Employee,
    Structure,
    fees,
    [Type of fees],
    Month,
    [Total TTC],
    Left(FileBaseName(), Len(FileBaseName())-8) as Company
FROM
//[http://community.qlik.com/servlet/JiveServlet/download/596844-122156/test%20companyA%2007%202014.xls...]

(ooxml, embedded labels, table is Example, filters(
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 4))
));

hope this helps

regards

Marco

[MarcoWedel · Accepted solution]

Hi again,

another possibility is to load the company from the file content seperate from the table and to join the two loads afterwards:

tabData:
LOAD Employee,
    Structure,
    fees,
    [Type of fees],
    Month,
    [Total TTC],
    Left(FileBaseName(), Len(FileBaseName())-8) as CompanyFromFileName,
    FileBaseName() as File
FROM
//[http://community.qlik.com/servlet/JiveServlet/download/596844-122156/test%20companyA%2007%202014.xls...]

(ooxml, embedded labels, table is Example, filters(
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 4))
));

tabTemp:
LOAD B as Company,
    FileBaseName() as File
From

(ooxml, no labels, table is Example)
Where RecNo() = 1;

Left Join (tabData)
LOAD *Resident tabTemp;

DROP Table tabTemp;

hope this helps

regards

Marco

[#4]

Thank you very much Marco, What if the file name doesn't contain the company name and I have to read the name from the cell 'B2"? Is there a way to do it?

[MarcoWedel]

I posted an example of that, but it's still in moderation state:

regards

Marco