How to fetch month and year from file name

[#1]

Hi,

I have a file name Table_5_2014

I have to take month and year from this file name and Day from one Column (Day) Present in file

[qlikoqlik]

Hi

There is a Filename function, you can use this either in the script or at the front end

Filename() as NameFile,right(FileName(),4) as NameFileRight

Thanks

Padma

[#3]

Thanks qlikoqlik This is very helpful but how will I attach the column with it so that my day column will look like

1-5-2014

2-5-2014

.

.

.

.30-5-2014

my Day column has

1

2

3

.

.

.

.

30

[MarcoWedel]

Load right(filebasename(), 4) as year,

          mid(filebasename(), 7, 2) as month,

          date

Form table_*.xlsx;

[qlikoqlik]

Hi

You can concatenate or may be you can use makedate function

right(FileName(),2)&'-'& right(FileName(),4) as CalcDate

Thanks and reards

Padma

[PrashantSangle]

Hi,

Try like this,

makedate(right(filebasename(),4),mid(filebasename(),7,2),date) as new_Date,

or

makedate(Subfield(filebasename(),'_',3),Subfield(filebasename(),'_',2),date) as new_Date,

Regards,

[#7]

Day&'-'& left(FileName(),2)&'-'& right(FileName(),4) as Date

Regards,

Dawar

[MarcoWedel]

maybe like this:

table1:
CrossTable (daytxt, data, 3)
LOAD Right(FileBaseName(), 4) as year,
    Mid(FileBaseName(), Len(FileBaseName())-6, 2)  as month,
    *
FROM [http://community.qlik.com/servlet/JiveServlet/download/594139-121437/Sachin_06_2014.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join (table1)
LOAD *,
    MakeDate(year, month, day) as date;
LOAD Distinct
    year,
    month,
    daytxt,
    Num#(daytxt) as day
Resident table1;

DROP Field daytxt;

hope this helps

regards

Marco