Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I received files without date inside, so I need to load it from filename, but problem that date in following format:
GenF JUNE WK 3.xlsx
In this case how I should determine week ending date for I example I know that JUNE WK 3 = 2015-06-20.
I think I should create calendar and then check the week number and month.
Please advise the best way.
Thanks,
Vitaliy
Hi,
as you are looking for 2015-06-20 as date of week 3 in June, I guess you are starting the week count at saturdays.
So one solution could be:
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
tabFileNames:
LOAD *,
MakeDate(2015,Month,Mod(5-WeekDay(MakeDate(2015,Month)),7)+MonthWeek*7-6) as Date;
LOAD *,
Month(Date#(SubField(FileBaseName,' ',-3),'MMMM')) as Month,
SubField(FileBaseName,' ',-1) as MonthWeek
INLINE [
FileBaseName
GenF JUNE WK 3
GenF JULY WK 2
GenF AUGUST WK 1
GenF SEPTEMBER WK 4
GenF OCTOBER WK 3
GenF NOVEMBER WK 1
];
hope this helps
regards
Marco
Have you checked
MakeWeekDate function?
at least you should have Year in file name. How would I come to know that file of JUNE week 3 is for year 2015 not for 2014?
LET vfile = 'GenF JUNE WK 3.xlsx';
LET vMonth = SubField('$(vfile)',' ',2);
LET vWeek = Left(SubField('$(vfile)',' ',4), Len(SubField('$(vfile)',' ',4)) - 5);
And then use date functions to find the date
Unfortunately I haven't year in filename
my idea is
get month number from file name (month)
get year (2015?) (year)
get number of week from filename (weeknum)
calc the week of the year (weekofyear):
week(monthstart( makedate(year, month) )) + (weeknum) - 1
use makeweekdate to calc the date from year and weekofyear
Also I try to use following script to determine month, but it works correctly only when month from file match with Mapping. Is this possible to compare map with data using '*', for example JUNE with JUN and APRIL with APR
MonthNumbers:
mapping LOAD * INLINE [
Month, Number
*JAN*, 01
*FEB*, 02
*MAR*, 03
*APR*, 04
*MAY*, 05
JUN, 06
JUL, 07
*AUG*, 08
*SEP*, 09
*OCT*, 10
*NOV*, 11
*DEC*, 12
];
LOAD
ApplyMap('MonthNumbers', Upper(subfield(FileName(), ' ', -3)),'NO MONTH MAPPED') as [MONTH_Number],
FROM
[$(vFile)]
(biff, no labels);
use wildmatch, from Qlik help
wildmatch( str, expr1 [ , expr2,...exprN ] )
The wildmatch function performs a case insensitive comparison and permits the use of wildcard characters ( * and ?) in the comparison strings.
Example:
wildmatch( M, 'ja*','fe?','mar')
returns 1 if M = January
returns 2 if M = fex
Hi,
as you are looking for 2015-06-20 as date of week 3 in June, I guess you are starting the week count at saturdays.
So one solution could be:
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
tabFileNames:
LOAD *,
MakeDate(2015,Month,Mod(5-WeekDay(MakeDate(2015,Month)),7)+MonthWeek*7-6) as Date;
LOAD *,
Month(Date#(SubField(FileBaseName,' ',-3),'MMMM')) as Month,
SubField(FileBaseName,' ',-1) as MonthWeek
INLINE [
FileBaseName
GenF JUNE WK 3
GenF JULY WK 2
GenF AUGUST WK 1
GenF SEPTEMBER WK 4
GenF OCTOBER WK 3
GenF NOVEMBER WK 1
];
hope this helps
regards
Marco