Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

madhumitha
Contributor

Excel file name as Primary Key

Hello All,

I have few excel files from which I should be extracting the data.

The excel file name is in the below format.

Test dashboard PKC00002 Health Checks_31May17.xlsx

While extracting data from the file, how do I extract the primary key from the name of the file. In the above example, it would be "PKC0002"

Thanks in advance.

Tags (1)
1 Solution

Accepted Solutions
sureshqv
Esteemed Contributor III

Re: Excel file name as Primary Key

Hi

check below code

LOAD Product_Id,

     Product_Line,

     Product_Subcategory,

     Product_category,

      FileBaseName() as FullName,

     subfield(FileBaseName(),' ',1) as FirstName

FROM

(biff, embedded labels, table is Sheet1$);

fbname.PNG

5 Replies
sureshqv
Esteemed Contributor III

Re: Excel file name as Primary Key

Hi you can try below with your code

a:

LOAD Product_Id,

     Product_Line,

     Product_Subcategory,

     Product_category,

     FileBaseName() as new,

     subfield(FileBaseName(),' ',1) as x

FROM

(biff, embedded labels, table is Sheet1$);

sureshqv
Esteemed Contributor III

Re: Excel file name as Primary Key

Hi

check below code

LOAD Product_Id,

     Product_Line,

     Product_Subcategory,

     Product_category,

      FileBaseName() as FullName,

     subfield(FileBaseName(),' ',1) as FirstName

FROM

(biff, embedded labels, table is Sheet1$);

fbname.PNG

techvarun
Valued Contributor II

Re: Excel file name as Primary Key

Use below code in load script

Subfield(FileBaseName(),' ',1)

madhumitha
Contributor

Re: Excel file name as Primary Key

Thanks Varun

It works Fine.

Re: Excel file name as Primary Key

Is they filenames coming from any external source as field? If so, SubField(FieldName, ' ',1) as FieldName

Life is so rich, and we need to respect to the life !!!
Community Browser