Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

vchuprina
Not applicable

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

1 Solution

Accepted Solutions
MarcoWedel
Not applicable

Re: How load data from file name?

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

14 Replies
MK_QSL
Not applicable

Re: How load data from file name?

Have you checked

MakeWeekDate function?

kush141087
Not applicable

Re: How load data from file name?

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
Not applicable

Re: How load data from file name?

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

giakoum
Not applicable

Re: How load data from file name?

vchuprina
Not applicable

Re: How load data from file name?

Unfortunately I haven't year in filename

maxgro
Not applicable

Re: How load data from file name?

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
Not applicable

Re: How load data from file name?

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

maxgro
Not applicable

Re: How load data from file name?

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
Not applicable

Re: How load data from file name?

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