Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Can any one please help me on this.
I am trying to extract year,Month and Date from the File Name like below.
Script:
GlobalITAssesment:
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)-1),mid(FileName(),40,2))) as MonthName,
(mid(FileName(),38,2)-1)&'/'&mid(FileName(),34,4) as Year_Month,
mid(FileName(),34,4) as Year,
mid(FileName(),38,2)-1 as Month,
mid(FileName(),40,2) as Day
FROM [lib://Source/*.xlsx]
(ooxml, embedded labels, table is Sheet1);
The above script is working fine in Oct and Nov Month files but coming to Jan Month file there is an issue.
When we are in January Month and Subtract -1 then its not finding December.
Please help me.
Thanks in advance.
May be try this
GlobalITAssesment:
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)-1),mid(FileName(),40,2))) as MonthName,
Date(MonthStart(makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2))), 'M/YYYY') as Year_Month,
mid(FileName(),34,4) as Year,
Num(Month(makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2)))) as Month,
mid(FileName(),40,2) as Day
FROM [lib://Source/*.xlsx]
(ooxml, embedded labels, table is Sheet1);
May be try this
GlobalITAssesment:
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)-1),mid(FileName(),40,2))) as MonthName,
Date(MonthStart(makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2))), 'M/YYYY') as Year_Month,
mid(FileName(),34,4) as Year,
Num(Month(makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2)))) as Month,
mid(FileName(),40,2) as Day
FROM [lib://Source/*.xlsx]
(ooxml, embedded labels, table is Sheet1);
even simpler would be to use Textbetween() if your preceeding and trailing text is static
load MonthName(Date#(Trim(TextBetween(StringToDate,'Apps ',' V1')),'YYYYMMDD')) as MonthIS inline [
StringToDate
Global IT Assesment - Pilot Apps 20171029 V1 -Oct Metrics
Global IT Assesment - Pilot Apps 20171118 V1 -Nov Metrics
Global IT Assesment - Pilot Apps 20180109 V1 -Jan Metrics
];
May be this
Date(MonthStart(makedate(mid(FileName(),34,4), mid(FileName(),38,2), mid(FileName(),40,2)), -1), 'MMM YYYY') as MonthName,
Please try with
GlobalITAssesment:
Load
*,
Year(Date) as Year,
MonthName(Date) as MonthName,
Month(Date) as Month;
LOAD
"OR",
"R",
A,
G,
Date#(TextBetween(BaseFileName(),'Apps ', ' V'),'YYYYMMDD') as Date
FROM [lib://Source/*.xlsx]
(ooxml, embedded labels, table is Sheet1);
Use this
Num(Month(MonthStart(makedate(mid(FileName(),34,4), mid(FileName(),38,2), mid(FileName(),40,2)), -1))) as Previous_Month,