Solved: Get Week Based on FileName - Qlik Community

Anonymous · ‎2015-10-06

Hi,

I have a question,

I have a variable and the value of variable is = Data_1549-1608.xlsx

Nb: 1549-1608 is yearweek range.

I want to get YearWeek Between that range and store it into variable.

The result i expect is,

1549, 1550, 1551, 1552, 1553, 1601, 1602, 1603, 1604, 1605, 1606, 1607, 1608

The problem is when the year transition from 2015 to 2016, anybody has any idea ?

Really appreciate your help,

Thanks

Digvijay_Singh · ‎2015-10-07

Quite possible -

Set vFileName = 'Data_1501-1513.xlsx';

Let vStartWeek = SubField(KeepChar(vFileName,'0123456789-'),'-',1);

Let vEndWeek = SubField(KeepChar(vFileName,'0123456789-'),'-',2);

Let vStartDate = Num(MakeWeekDate('20' & left('$(vStartWeek)',2),right('$(vStartWeek)',2),1));

Let vEndDate = Num(MakeWeekDate('20' & left('$(vEndWeek)',2),right('$(vEndWeek)',2),1));

Table:

Load distinct right(WeekYear(TempDate),2) & num(Week(TempDate),'00') as WeekRange;

Load $(vStartDate)+Iterno()-1 as TempDate

AutoGenerate(1)

While $(vStartDate) +Iterno()-1<=$(vEndDate);

NoConcatenate

Table1:

Load * resident Table Order By WeekRange;

Drop Table Table;

For i = 1 to NoOfRows('Table1')

Let vVar$(i) = Peek('WeekRange',$(i),'Table1');

Next i

View solution in original post

sunny_talwar · ‎2015-10-06

Try this expression:

=Concat(SubField(KeepChar(vVar1, '0123456789-'), '-', 1) +

ValueLoop(0, $(=Ceil((365 - Right(SubField(KeepChar(vVar1, '0123456789-'), '-', 1), 2)*7)/7))), ', ') & ', ' &

Concat(SubField(KeepChar(vVar1, '0123456789-'), '-', 2) +

ValueLoop($(=1-Right(SubField(KeepChar(vVar1, '0123456789-'), '-', 2), 2)), 0), ', ')

Output in a text box object:

sunny_talwar · ‎2015-10-06

This will only work with two consecutive years like 2015 and 2016 or 2014 and 2015 and so on.... if you can have more than two years of data, you will need to further refine the formula.

HTH

Best,

Sunny

sunny_talwar · ‎2015-10-06

Anonymous · ‎2015-10-06

Hi Sunny

Thanks for your response and it gives me the result i expect.

But do you have more simple idea to solve this problem ?

Thanks

sunny_talwar · ‎2015-10-06

Let me see if I can figure out something easier and simpler

Anonymous · ‎2015-10-06

Hi , I also have a problem when the file name is Data_1501-1513.xlsx

The result is

The result i expect is

1501, 1502, 1503, 1504, 1505, 1506, 1507, 1508, 1509, 1510, 1511, 1512, 1513

sunny_talwar · ‎2015-10-06

See if this is better:

vVar1: Data_1549-1608.xlsx

vVar2:

=MakeWeekDate(Num#(20 & Left(SubField(KeepChar(vVar1, '0123456789-'), '-', 2), 2), '##'), Right(SubField(KeepChar(vVar1, '0123456789-'), '-', 2), 2)) -

MakeWeekDate(Num#(20 & Left(SubField(KeepChar(vVar1, '0123456789-'), '-', 1), 2), '##'), Right(SubField(KeepChar(vVar1, '0123456789-'), '-', 1), 2))

vVar3: =Num(MakeWeekDate(Num#(20 & Left(SubField(KeepChar(vVar1, '0123456789-'), '-', 1), 2), '##'), Right(SubField(KeepChar(vVar1, '0123456789-'), '-', 1), 2), 3))

The expression in text box:

=Concat(Right(Year(vVar3 + ValueLoop(0, $(=vVar2), 7)), 2) &

Num(Week(vVar3 + ValueLoop(0, $(=vVar2), 7)), '00') , ', ', vVar3 +

ValueLoop(0, $(=vVar2), 7))

Output:

Changing vVar1 to -> Data_1501-1513.xlsx

Anonymous · ‎2015-10-07

Hi Sunny

Thanks for your suggestion.

Please tell me if there is another easier way.

Thanks

sunny_talwar · ‎2015-10-07

I will continue to look for another better way.

Best,

Sunny