Get Week Based on FileName

[Anonymous]

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]

We can make simpler if script changes are allowed.

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 concat(distinct right(WeekYear(TempDate),2) & num(Week(TempDate),'00'),',') as WeekRange;

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

AutoGenerate(1)

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

[sunny_talwar]

I was not aware of WeekYear() function. I think that will help resolve an issue I was facing with my method. But this is definitely a very good apporach Digvijay.

Thanks for sharing this.

Best,

Sunny

[Anonymous]

Hi Digvijay,

Thanks for your suggestion,

But how if i want to store each YearWeek to variable.

So it will be

vVar1 = 1549

vVar2 = 1550

vVar3 = 1551

vVar4 = 1552

vVar5 = 1553

vVar6 = 1601

.

.

vVar13 = 1608

Is it possible ?

Thanks

Really appreciate your help

[Digvijay_Singh · Accepted solution]

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

[Anonymous]

Hi,

Great Idea Digvijay,

You're a great problem solver.

Thanks in advance

[Digvijay_Singh]

I am glad it helped you in some way.

Thanks,

Digvijay

[sunny_talwar]

I think you should change the correct answer from mine to Digvijay's because that way you will be rewarding him for his good work and will also be providing the future readers with the correct solution to look at.

Thank you,

Sunny