Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
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.
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;
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);
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!