topic Re: FILE TITLE AS FIELD VALUE in QlikView

FILE TITLE AS FIELD VALUE

Thu, 24 Sep 2015 09:56:21 GMT

Hello Commnunity,

I want to do somtething a bit special.

I have this as files :

the file name contain the year of the data, but in the excel table itself the year field doesnt exist.

I want to know if its possible to extract that year from the file name et create a year field directly in the script.

Thanks

Re: FILE TITLE AS FIELD VALUE

SreeniJD — Thu, 24 Sep 2015 10:01:31 GMT

Hi There,

Create a seperate table by loading all files with a loop and store the titles as field names in that table then join this with applymap.

Hope this help

Sreeni

Re: FILE TITLE AS FIELD VALUE

Thu, 24 Sep 2015 10:04:13 GMT

Hi Jd Sreeni,

Can you give me an example (maybe the script) pleasE?

Thnaks

Re: FILE TITLE AS FIELD VALUE

swuehl — Thu, 24 Sep 2015 10:05:40 GMT

Try something like

LOAD

TextBetween(FileBaseName(), '-','.') as Year,

...

FROM Excel-2009.xls

...;

Re: FILE TITLE AS FIELD VALUE

alexandros17 — Thu, 24 Sep 2015 10:06:24 GMT

Yes, when you load the instruction FileName() returns the name of the file , so

Load

....

Right(filename(),4) as Year

....

Resident

....

Re: FILE TITLE AS FIELD VALUE

Gabriel — Thu, 24 Sep 2015 10:14:22 GMT

Hi,

Year you can use FileName() Function to extract the File Name and use Subfield function to extract the part that you need.

Example

Tab1:

LOAD

...... ,

......,

FileName() AS File_Name

,Subfield(FileName(),'-',2) AS File_Year

FROM

......SourceFile.xls;

Re: FILE TITLE AS FIELD VALUE

Thu, 24 Sep 2015 10:17:22 GMT

include --> Date#(LEFT(right(Filename(),8),4),'YYYY') as Year in your Load Statement

You can include some functions in the load, like this:

Load *, filename() as Filename, Date#(LEFT(right(Filename(),8),4),'YYYY') as Year

From 12015.txt (TXT);

Re: FILE TITLE AS FIELD VALUE

Thu, 24 Sep 2015 10:48:16 GMT

please close the thread by marking the correct answer / helpful answers