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").
1 Solution

Accepted Solutions
MarcoWedel

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:

QlikCommunity_Thread_169592_Pic1.JPG

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

View solution in original post

14 Replies
MK_QSL
MVP
MVP

Have you checked

MakeWeekDate function?

Kushal_Chawda

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?

giakoum
Partner - Master II
Partner - Master II

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

vchuprina
Specialist
Specialist
Author

Unfortunately I haven't year in filename

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").
maxgro
MVP
MVP

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


vchuprina
Specialist
Specialist
Author

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

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").
maxgro
MVP
MVP

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

MarcoWedel

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:

QlikCommunity_Thread_169592_Pic1.JPG

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