Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
bhavvibudagam
Contributor II

How to extract Month,Year,YearMonth fields from Excel file name?

Hi Experts,

I have two .xlsx files named

Global IT Assesment - vilot Apps 20171029 V1 -Oct Metrics

Global IT Assesment - vilot Apps 20171118 V1 -Nov Metrics

From these excel file names need to extract the Date,Month,Year and YearMonth fields in the script.

Please help me how to extract the above date fields from the file name.

Please find the excel source files.

Thanks in advance.

1 Solution

Accepted Solutions
micheledenardi
Valued Contributor

Re: How to extract Month,Year,YearMonth fields from Excel file name?

use FileName() Funcion to get the xlsx name and then use string and date functions to get the value you want.

2017-12-04 12_10_28-QlikView x64 Personal Edition - [C__Users_Michele.Denardi_Desktop_Test.qvw_].png

Global IT Assesment:

LOAD

    OR,

     R,

     A,

     G,

     makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2)) as Date,

     monthname(makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2))) as MonthName,

     mid(FileName(),38,2)&'/'&mid(FileName(),34,4) as Year_Month,

     mid(FileName(),34,4) as Year,

     mid(FileName(),38,2) as Month,

     mid(FileName(),40,2) as Day

FROM

(ooxml, embedded labels, table is Sheet1);

Apply the same logic for the second xlsx

4 Replies
devarasu07
Honored Contributor II

Re: How to extract Month,Year,YearMonth fields from Excel file name?

Re: How to extract Month,Year,YearMonth fields from Excel file name?

First you need to load all data to make good data model. That model may from fact table. From fact table, You can extract and develop only one date field after you can use Month(), Year() .... for DateField..

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
micheledenardi
Valued Contributor

Re: How to extract Month,Year,YearMonth fields from Excel file name?

use FileName() Funcion to get the xlsx name and then use string and date functions to get the value you want.

2017-12-04 12_10_28-QlikView x64 Personal Edition - [C__Users_Michele.Denardi_Desktop_Test.qvw_].png

Global IT Assesment:

LOAD

    OR,

     R,

     A,

     G,

     makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2)) as Date,

     monthname(makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2))) as MonthName,

     mid(FileName(),38,2)&'/'&mid(FileName(),34,4) as Year_Month,

     mid(FileName(),34,4) as Year,

     mid(FileName(),38,2) as Month,

     mid(FileName(),40,2) as Day

FROM

(ooxml, embedded labels, table is Sheet1);

Apply the same logic for the second xlsx

Highlighted
antoniotiman
Honored Contributor III

Re: How to extract Month,Year,YearMonth fields from Excel file name?

May be this

SET DateFormat='YYYYMMDD';

LOAD *,Month(Date) as Month,Year(Date) as Year,MonthName(Date) as MonthName
Where Not IsNull(Date);
LOAD *,Date(Alt(Date#(SubField(FileName(),' ')))) as Date
FROM
[Global IT Assesment - vilot Apps 20171029 V1 -Oct Metrics.xlsx]
(ooxml, embedded labels, table is
Sheet1);