Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
vchuprina
Specialist
Specialist

How load data from file name?

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

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
14 Replies
MarcoWedel

just to make sure:

you know that there is a function FileTime() that returns the "last modified" timestamp of a file?

regards

Marco

maxgro
MVP
MVP

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

let filename='GenF JUNE WK 3.xlsx';

let year=year(today());

let month=num(month(Date#($(year) & subfield('$(filename)', ' ', 2) & '01', 'YYYYMMMMDD')));

let weeknum=replace(subfield('$(filename)', ' ', 4), '.xlsx', '');

let weeknum=week(monthstart( makedate(year, month)   ))    +   (weeknum) - 1;

let date=date(MakeWeekDate($(year), $(weeknum))+5);

EDIT

this line doesn't work for May (I think because the format is MMMM and May is 3 char

let month=num(month(Date#($(year) & subfield('$(filename)', ' ', 2) & '01', 'YYYYMMMMDD')));

this works for May too

let month=num(month(Date#($(year) & '-' & subfield('$(filename)', ' ', 2) & '-' & '01', 'YYYY-MMMM-DD')));

vchuprina
Specialist
Specialist
Author

Thank you it works good, but I find on thing, for example May week 4 in this logic = 2015/05/23 .

In my calendar it should be 2015/05/30. Could you advise what should I do?

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
MarcoWedel

2015/05/30 is the fifth saturday in may.

please specify your month-week count.


regards


Marco

vchuprina
Specialist
Specialist
Author

Marco,

We use NRF calendar.

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").