topic Re: Excel file name as Primary Key in QlikView

Excel file name as Primary Key

madhumitha — Thu, 09 Nov 2017 05:45:05 GMT

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.

Chanty4u — Thu, 09 Nov 2017 05:54:13 GMT

Hi you can try below with your code

LOAD Product_Id,

Product_Line,

Product_Subcategory,

Product_category,

FileBaseName() as new,

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

FROM

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

Chanty4u — Thu, 09 Nov 2017 05:57:43 GMT

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

techvarun — Thu, 09 Nov 2017 05:57:48 GMT

Use below code in load script

Subfield(FileBaseName(),' ',1)

madhumitha — Thu, 09 Nov 2017 06:05:11 GMT

Thanks Varun

It works Fine.

Anil_Babu_Samineni — Thu, 09 Nov 2017 06:06:09 GMT

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