topic Re: How load data from file name? in QlikView

How load data from file name?

vchuprina — Tue, 23 Jun 2015 12:10:42 GMT

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

Re: How load data from file name?

MK_QSL — Tue, 23 Jun 2015 12:14:27 GMT

Have you checked

MakeWeekDate function?

Re: How load data from file name?

Kushal_Chawda — Tue, 23 Jun 2015 12:19:24 GMT

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?

Re: How load data from file name?

giakoum — Tue, 23 Jun 2015 12:19:35 GMT

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

Re: How load data from file name?

giakoum — Tue, 23 Jun 2015 12:22:53 GMT

Calculate weekend date from week and year

Re: How load data from file name?

vchuprina — Tue, 23 Jun 2015 12:23:24 GMT

Unfortunately I haven't year in filename

Re: How load data from file name?

maxgro — Tue, 23 Jun 2015 12:33:14 GMT

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

Re: How load data from file name?

vchuprina — Tue, 23 Jun 2015 20:44:22 GMT

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

Re: How load data from file name?

maxgro — Tue, 23 Jun 2015 20:52:22 GMT

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

Re: How load data from file name?

MarcoWedel — Tue, 23 Jun 2015 22:04:20 GMT

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

Re: How load data from file name?

MarcoWedel — Tue, 23 Jun 2015 22:08:49 GMT

just to make sure:

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

regards

Marco

Re: How load data from file name?

maxgro — Tue, 23 Jun 2015 22:21:25 GMT

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

Re: How load data from file name?

vchuprina — Fri, 26 Jun 2015 14:05:44 GMT

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?

Re: How load data from file name?

MarcoWedel — Fri, 26 Jun 2015 16:04:21 GMT

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

please specify your month-week count.

regards

Marco

Re: How load data from file name?

vchuprina — Fri, 26 Jun 2015 19:20:35 GMT

Marco,

We use NRF calendar.