Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Read cell value and append it to an additional column?

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!

Tags (4)
1 Solution

Accepted Solutions

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

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;

QlikCommunity_Thread_131051_Pic3.JPG.jpg

hope this helps

regards

Marco

4 Replies

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

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))

));

QlikCommunity_Thread_131051_Pic1.JPG.jpg

QlikCommunity_Thread_131051_Pic2.JPG.jpg

hope this helps

regards

Marco

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

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;

QlikCommunity_Thread_131051_Pic3.JPG.jpg

hope this helps

regards

Marco

Not applicable

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

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?

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

QlikCommunity_Thread_131051_Pic4.JPG.jpg

QlikCommunity_Thread_131051_Pic5.JPG.jpg

regards

Marco

Community Browser