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

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

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

devarasu07 — Mon, 04 Dec 2017 11:03:33 GMT

Hi,

you can try like this

Making Month/Year Date From Source File Name

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

Anil_Babu_Samineni — Mon, 04 Dec 2017 11:11:24 GMT

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..

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

micheledenardi — Mon, 04 Dec 2017 11:11:54 GMT

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

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

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

antoniotiman — Mon, 04 Dec 2017 11:25:42 GMT

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