topic Re: Get Week Based on FileName in QlikView

Get Week Based on FileName

Anonymous — Wed, 07 Oct 2015 01:41:56 GMT

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

Re: Get Week Based on FileName

sunny_talwar — Wed, 07 Oct 2015 02:06:18 GMT

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:

Re: Get Week Based on FileName

sunny_talwar — Wed, 07 Oct 2015 02:07:19 GMT

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

Re: Get Week Based on FileName

sunny_talwar — Wed, 07 Oct 2015 02:08:12 GMT

Re: Get Week Based on FileName

Anonymous — Wed, 07 Oct 2015 02:15:20 GMT

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

Re: Get Week Based on FileName

sunny_talwar — Wed, 07 Oct 2015 02:19:49 GMT

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

Re: Get Week Based on FileName

Anonymous — Wed, 07 Oct 2015 02:20:37 GMT

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

Re: Get Week Based on FileName

sunny_talwar — Wed, 07 Oct 2015 03:04:31 GMT

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

Re: Get Week Based on FileName

Anonymous — Wed, 07 Oct 2015 04:07:44 GMT

Hi Sunny

Thanks for your suggestion.

Please tell me if there is another easier way.

Thanks

Re: Get Week Based on FileName

sunny_talwar — Wed, 07 Oct 2015 04:17:16 GMT

I will continue to look for another better way.

Best,

Sunny

Re: Get Week Based on FileName

Digvijay_Singh — Wed, 07 Oct 2015 05:09:45 GMT

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

Re: Get Week Based on FileName

sunny_talwar — Wed, 07 Oct 2015 05:14:33 GMT

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

Re: Get Week Based on FileName

Anonymous — Wed, 07 Oct 2015 06:31:51 GMT

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

Re: Get Week Based on FileName

Digvijay_Singh — Wed, 07 Oct 2015 06:52:38 GMT

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

Re: Get Week Based on FileName

Anonymous — Wed, 07 Oct 2015 07:51:44 GMT

Hi,

Great Idea Digvijay,

You're a great problem solver.

Thanks in advance

Re: Get Week Based on FileName

Digvijay_Singh — Wed, 07 Oct 2015 07:56:04 GMT

I am glad it helped you in some way.

Thanks,

Digvijay

Re: Get Week Based on FileName

sunny_talwar — Wed, 07 Oct 2015 13:13:27 GMT

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