topic Read cell value and append it to an additional column? in QlikView

Read cell value and append it to an additional column?

Wed, 27 Aug 2014 19:14:58 GMT

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!

Re: Read cell value and append it to an additional column?

MarcoWedel — Wed, 27 Aug 2014 22:02:16 GMT

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

(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

Re: Read cell value and append it to an additional column?

MarcoWedel — Wed, 27 Aug 2014 22:18:38 GMT

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

(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

Re: Read cell value and append it to an additional column?

Thu, 28 Aug 2014 08:23:53 GMT

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?

Re: Read cell value and append it to an additional column?

MarcoWedel — Thu, 28 Aug 2014 11:22:24 GMT

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

regards

Marco