Invalid Path

[#1]

Hello everyone & Thanks for your contribution!

I have to built a Calendar with tables from MS SQL. I'm administrator of the database.

I tried Do While Loop, Generate etc, but unfortunately I always get an error 'Invalid Path', at the CalTemp2:

I need a table that will start will:

Fiscal Year   - Period

2015                          1

2015                          2

2015                          3

2015                          4 

......                              ....

2016                           1    etc

Depending of the company it could have 12 or 13 Periods / Year, so that's why I can't simply insert the periods.

In comment you will see all my attemps,

Please help!! Thanks!

LIB CONNECT TO 'SQLEXPRESS';

GL01:

LOAD OPTIONID,

    PHONENBR,

    FAX,

    COMPANYID,

    CONTACT,

    CLOSESEG,

    ACCTSEG,

    ABRKDFLT,

    ABRKDELM,

    SWBTCHEDIT,

    SWPROVPST,

    SWPRTPBTCH,

    CODELOCK1,

    CODELOCK2,

    CODELOCK3,

    CODELOCK4,

    CODELOCK5,

    LOCKFILL,

    SWQTY,

    QTYDEC,

    YRSHIST,

    YRACCTDEL,

    NEXTBTCHNO,

    PSTSQ,

    PROVPSTSQ,

    PJRNLPRGTO,

    BTCHPSTTO,

    JRNLPRGTO,

    FLOOR(YRCLSLST) as FSCYEAR,

    YRLSTACTL,

    PRDNOPSTPR,

    YRNOPSTPR,

    REACCT,

    SWMC,

    DFLRATETYP,

    SWACCTGRP,

    SWPRVYRPST,

    YRSTRANDTL,

    HSTCLRACCT,

    RPACCT,

    SRCETYPE,

    SWUSESEC,

    SWDEFACCSS;

SQL SELECT OPTIONID,

    PHONENBR,

    FAX,

    COMPANYID,

    CONTACT,

    CLOSESEG,

    ACCTSEG,

    ABRKDFLT,

    ABRKDELM,

    SWBTCHEDIT,

    SWPROVPST,

    SWPRTPBTCH,

    CODELOCK1,

    CODELOCK2,

    CODELOCK3,

    CODELOCK4,

    CODELOCK5,

    LOCKFILL,

    SWQTY,

    QTYDEC,

    YRSHIST,

    YRACCTDEL,

    NEXTBTCHNO,

    PSTSQ,

    PROVPSTSQ,

    PJRNLPRGTO,

    BTCHPSTTO,

    JRNLPRGTO,

    YRCLSLST,

    YRLSTACTL,

    PRDNOPSTPR,

    YRNOPSTPR,

    REACCT,

    SWMC,

    DFLRATETYP,

    SWACCTGRP,

    SWPRVYRPST,

    YRSTRANDTL,

    HSTCLRACCT,

    RPACCT,

    SRCETYPE,

    SWUSESEC,

    SWDEFACCSS

FROM OCT53D.dbo.GL01;

LIB CONNECT TO 'SQLEXPRESS';

CSFSC:

LOAD FSCYEAR,

    PERIODS,

    QTR4PERD,

    ACTIVE,

    BGNDATE1,

    BGNDATE2,

    BGNDATE3,

    BGNDATE4,

    BGNDATE5,

    BGNDATE6,

    BGNDATE7,

    BGNDATE8,

    BGNDATE9,

    BGNDATE10,

    BGNDATE11,

    BGNDATE12,

    BGNDATE13,

    ENDDATE1,

    ENDDATE2,

    ENDDATE3,

    ENDDATE4,

    ENDDATE5,

    ENDDATE6,

    ENDDATE7,

    ENDDATE8,

    ENDDATE9,

    ENDDATE10,

    ENDDATE11,

    ENDDATE12,

    ENDDATE13;

SQL SELECT FSCYEAR,

    PERIODS,

    QTR4PERD,

    ACTIVE,

    BGNDATE1,

    BGNDATE2,

    BGNDATE3,

    BGNDATE4,

    BGNDATE5,

    BGNDATE6,

    BGNDATE7,

    BGNDATE8,

    BGNDATE9,

    BGNDATE10,

    BGNDATE11,

    BGNDATE12,

    BGNDATE13,

    ENDDATE1,

    ENDDATE2,

    ENDDATE3,

    ENDDATE4,

    ENDDATE5,

    ENDDATE6,

    ENDDATE7,

    ENDDATE8,

    ENDDATE9,

    ENDDATE10,

    ENDDATE11,

    ENDDATE12,

    ENDDATE13

FROM OCT53D.dbo.CSFSC;

LIB CONNECT TO 'SQLEXPRESS';

MinMaxYear:

Load

  MIN(FSCYEAR) AS MinYear,

  MAX(FSCYEAR) AS MaxYear

RESIDENT CSFSC;

// Create variables with Min and Max Year & Period Calendar

LET vMinYear = NUM(PEEK('MinYear',0,'MinMaxYear'));

LET vMaxYear = NUM(PEEK('MaxYear',0,'MinMaxYear'));

LET vYear = $(vMaxYear);

// Generate a single table with one field containing

// all existing Fiscal Year between vMinYear & vMaxYear.

CalTemp:

LOAD

  ($(vMinYear) + ROWNO() -1) AS TempYear

AutoGenerate

  $(vMaxYear) - $(vMinYear) +1;

// _________________________________________________________________

LIB CONNECT TO 'SQLEXPRESS';

YearPeriods:

Load

  FSCYEAR AS vFSCYEAR,

  PERIODS AS vPeriods

RESIDENT CSFSC;

// Create variables with Min and Max Year & Period Calendar

LET vMinPeriods = 1;

LET vFSCYEAR = NUM(PEEK('vFSCYEAR',0,'YearPeriods'));

LET vPeriods = NUM(PEEK('vPeriods',0,'YearPeriods'));

// LET vYear = $(vMaxYear);

// Generate a single table with two fields containing

// all existing Fiscal Year & Period between 12 & 13 periods.

// LIB CONNECT TO 'SQLEXPRESS';

CalTemp2:

  Load * from YearPeriod;

  $(TempYear) as TempYear,

    ($(vMinPeriods) + ROWNO() -1) AS TempPeriod

  AutoGenerate

    $(vPeriods) - $(vMinPeriods) +1;

// Do While $(vMinYear) <= $(vMaxYear)

// Load $(vMinYear) as vFSCYEAR,

//   ($(vMinPeriods) + ROWNO() -1) AS TempPeriod

// AutoGenerate

//   $(vPeriods) - $(vMinPeriods) +1;

// Let $(vMinYear) = $(vMinYear) +1;

// Loop

[Gysbert_Wassenaar]

CalTemp2:

  Load * from YearPeriod;

  $(TempYear) as TempYear,

    ($(vMinPeriods) + ROWNO() -1) AS TempPeriod

  AutoGenerate

    $(vPeriods) - $(vMinPeriods) +1;

The first two lines create a table called CalTemp2

The rest after the semicolon after YearPeriod is missing a LOAD keyword. Unless you're trying to do something I don't understand.

[swuehl]

Well spotted, Gysbert.

YearPeriod is also not a valid table source, if you want to refer to your YearPeriods table in the data model, you'll need to use a RESIDENT Load.

[#4 · Accepted solution]

Hello again,

& Thanks for your contribution Gysbert & Swuehl!

I've made a clean up and here's my new script.

My goal is to get a table that has a first column with the Year (FSCYEAR), second column the Period and it should look like this:

2015  1

2015  2

...        ...

2015  12

2016    1

2016    2

....        ....

My section CalTemp2: works if I comment the line FSCYEAR, I get a row with all the periods from 1 to 12 but I'm not able to get the FSCYEAR in the first column. I get an error :

The following error occurred:

Field not found - <FSCYEAR>

The error occurred here:

CalTemp2: Load FSCYEAR, (1 + ROWNO() -1) AS vPeriods AutoGenerate 12 - 1 +1

Would you use 'Do while Loop' instead? what would be your coding for it? I have many column to add after the period.

Thanks a lot for your help Guys!

My Code:

________________________________

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LIB CONNECT TO 'SQLEXPRESS';

CSFSC:

LOAD FSCYEAR,

    PERIODS,

....

SQL SELECT FSCYEAR,

    PERIODS,

...

FROM OCT53D.dbo.CSFSC;

// This is a test

YearPeriods:

Load

  FSCYEAR AS vFSCYEAR,

  PERIODS AS vPeriods

RESIDENT CSFSC;

// I need to know if I have 12 or 13 periods per year

MaxPeriods:

Load

  MAX(PERIODS) AS MaxPeriods

RESIDENT CSFSC;

// Create variables with Min and Max Year & Period Calendar

LET vMinPeriods = 1;

LET vFSCYEAR = NUM(PEEK('FSCYEAR',0,'CSFSC'));

LET vMaxPeriods = NUM(PEEK('MaxPeriods',0,'MaxPeriods'));

// Generate a single table with two fields containing

// all existing Fiscal Year & Periods from 1 to 12 (or 13) depending of the company.

CalTemp2:

Load

// FSCYEAR,

    ($(vMinPeriods) + ROWNO() -1) AS vPeriods

AutoGenerate

     $(vMaxPeriods) - $(vMinPeriods) +1;

[swuehl]

In a load statement, you can only reference fields that are part of the input table.

When using an Autogenerate statement, there is no real input table, hence you can't reference field FSCYEAR from your resident table CSFCS without using inter record functions.

Maybe LOAD your FSCYEAR from your resident table and create the periods using a WHILE clause?

CalTemp2:

Load Distinct

     FSCYEAR,

    ($(vMinPeriods) + iterno() -1) AS vPeriods

RESIDENT CSFCS

WHILE   ($(vMinPeriods) + iterno() -1) <= $(vMaxPeriods);

[#6]

Thanks a million times Swuehl! (And Gysbert)

I've been searching for two days, to get this simple task to work (I guess I have a long way to go ), but luckly I know that I can count on you guys.

Thanks again and have a great weekend!