replacing data in the script

[#1]

I have an issue I'm wondering if anyone can help with. I have 2 different sources I'm trying to load data from. Within each of these tables are fields for week number. One of the tables the week number corresponds to the calendar year and the other table the week number is our fiscal week number. So in one table week number 1 is actually week number 32 in another table. Is it possible in the load script to change the week number so they both match? If so how would I do that? Thanks

[#2]

Hi,

is there a formula you can recalculate the calendar week to the fiscal week? Uf yeas you could use that formula to calculate the fiscal week based on the calendar week.

Next, if both tables contains a datefield you could load that datefield an the fiscal week from the first table and then using APPLYMAP() the corresponding date with the fiscal week found by APPLYMAP. Needs to have a datefield in table one (whit fiscal week) for each datefield in table two

3rd version: create a new Excel Sheet with two Columns.

First Row

Year_CalendarWeek

Second Row:

Fiscal Week

Then load that Excelsheet as a mapping table.
Because of a bug in QV, your not able to use Excel Data direct as a mapping table.
So, first load the two fields which should be used into a temporary table and than load all the data off that temp. table into the mapping table. Dont forgett to delete that temp table.

While loading the table you want to exchange the calendar week to fiscal week, use APPLYMAP ('name of your mapping table', Year & '_' & CalWeek, 'No Fiscal Week found') AS Fiscalweek.

So you get a new field with the correct fiscal year.

if you give both field the same name you could use is as a link between the tables. I suggest to use a field based of the year and the week. Otherwise, you have to select the year in both tables 😉

Good luck

Michael

[#3]

Unfortunately, there isn't a date field in both tables only the table that has the week number as the calendar week. I tried the following formula to calculate the fiscal week number.

If

(week(DF_BusinessDay)<='21',week(DF_BusinessDay)+31,week(DF_BusinessDay)-21) as Week Number

I get an error though when I try to reload, what's wrong with this formula?

[#4]

Try the following (quite similar to yours, minor changes only):

If(week(DF_BusinessDay)<22,week(DF_BusinessDay)+31,week(DF_BusinessDay)-21) as WeekNumber

Also remember that DF_BusinessDay has to be a properly formatted date.

BR

[#5]

I was able to get it to work by simply changing Week Number to WeekNumber on my original formula.

Thanks for the help!