Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi together,
i have several SAP tables which i joined with an outer join.
Now i wanted to edit one little thing in the script and unlocked it. When i load the script again an error occurs, that a table could not be found, but i did not change anything.
Hi Jordy,
thanks a lot. i changed the code now to this:
"
Set dataManagerTables = '','Mastertable','/BIC/ARTD00012','/BIC/ARTD00022','/BIC/ARTD00042','/BIC/ARTD00051','/BIC/ARTD00061','/BIC/ARTD00081','/BIC/ARTD00091','/BIC/ARTD00111','Upload_final';
//This block renames script tables from non generated section which conflict with the names of managed tables
For each name in $(dataManagerTables)
Let index = 0;
Let currentName = name;
Let tableNumber = TableNumber(name);
Let matches = 0;
Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
index = index + 1;
currentName = name & '-' & index;
tableNumber = TableNumber(currentName)
matches = Match('$(currentName)', $(dataManagerTables));
Loop
If index > 0 then
Rename Table '$(name)' to '$(currentName)';
EndIf;
Next;
Set dataManagerTables = ;
Unqualify *;
__cityAliasesBase:
LOAD
Alias AS [__City],
geoKey AS [__geoKey],
CountryCode AS [__CityCountryCode]
FROM [lib://AttachedFiles/cityAliases.qvd]
(qvd);
__cityGeoBase:
LOAD
geoKey AS [__geoKey],
geoPoint AS [__GeoPoint]
FROM [lib://AttachedFiles/cityGeo.qvd]
(qvd);
__cityName2Key:
MAPPING LOAD
__City,
__geoKey
RESIDENT __cityAliasesBase;
__cityKey2GeoPoint:
MAPPING LOAD
__geoKey,
__GeoPoint
RESIDENT __cityGeoBase;
LIB CONNECT TO 'SapSql';
[/BIC/ARTD00012]:
LOAD DATE0,
…
SELECT DATE0,
…
FROM /BIC/ARTD00012;
LIB CONNECT TO 'SapSql';
[/BIC/ARTD00022]:
LOAD DATE0,
…
SELECT DATE0,
..
FROM /BIC/ARTD00022;
// STORE * FROM [/BIC/ARTD00022] INTO [LIB://FolderConnection/_BIC_ARTD00022.qvd];
// DROP TABLE [/BIC/ARTD00022];
OUTER JOIN ([/BIC/ARTD00012_]) LOAD
…
RESIDENT [/BIC/ARTD00022];
DROP TABLE [/BIC/ARTD00022];
LIB CONNECT TO 'SapSql';
[/BIC/ARTD00042]:
LOAD CHRT_ACCTS,
…
SELECT CHRT_ACCTS,
…
FROM /BIC/ARTD00042;
// STORE * FROM [/BIC/ARTD00042] INTO [LIB://FolderConnection/_BIC_ARTD00042.qvd];
// DROP TABLE [/BIC/ARTD00042];
OUTER JOIN ([/BIC/ARTD00012_]) LOAD
…
RESIDENT [/BIC/ARTD00042];
DROP TABLE [/BIC/ARTD00042];
LIB CONNECT TO 'SapSql';
[/BIC/ARTD00051]:
LOAD REQTSN,
…
SELECT REQTSN,
…
FROM /BIC/ARTD00051;
// STORE * FROM [/BIC/ARTD00051] INTO [LIB://FolderConnection/_BIC_ARTD00051.qvd];
// DROP TABLE [/BIC/ARTD00051];
OUTER JOIN ([/BIC/ARTD00012_]) LOAD
…
RESIDENT [/BIC/ARTD00051];
DROP TABLE [/BIC/ARTD00051];
LIB CONNECT TO 'SapSql';
[/BIC/ARTD00061]:
LOAD REQTSN,
…
SELECT REQTSN,
…
FROM /BIC/ARTD00061;
// STORE * FROM [/BIC/ARTD00061] INTO [LIB://FolderConnection/_BIC_ARTD00061.qvd];
// DROP TABLE [/BIC/ARTD00061];
OUTER JOIN ([/BIC/ARTD00012_]) LOAD
…
RESIDENT [/BIC/ARTD00061];
DROP TABLE [/BIC/ARTD00061];
LIB CONNECT TO 'SapSql';
[/BIC/ARTD00081]:
LOAD REQTSN,
…
SELECT REQTSN,
…
FROM /BIC/ARTD00081;
// STORE * FROM [/BIC/ARTD00081] INTO [LIB://FolderConnection/_BIC_ARTD00081.qvd];
// DROP TABLE [/BIC/ARTD00081];
OUTER JOIN ([/BIC/ARTD00012_]) LOAD
…
RESIDENT [/BIC/ARTD00081];
DROP TABLE [/BIC/ARTD00081];
LIB CONNECT TO 'SapSql';
[/BIC/ARTD00091]:
LOAD REQTSN,
…
SELECT REQTSN,
…
FROM /BIC/ARTD00091;
// STORE * FROM [/BIC/ARTD00091] INTO [LIB://FolderConnection/_BIC_ARTD00091.qvd];
// DROP TABLE [/BIC/ARTD00091];
OUTER JOIN ([/BIC/ARTD00012_]) LOAD
…
RESIDENT [/BIC/ARTD00091];
DROP TABLE [/BIC/ARTD00091];
LIB CONNECT TO 'SapSql';
[/BIC/ARTD00111]:
LOAD REQTSN,
…
SELECT REQTSN,
…
FROM /BIC/ARTD00111;
// STORE * FROM [/BIC/ARTD00111] INTO [LIB://FolderConnection/_BIC_ARTD00111.qvd];
// DROP TABLE [/BIC/ARTD00111];
OUTER JOIN ([/BIC/ARTD00012_]) LOAD
…
RESIDENT [/BIC/ARTD00111];
DROP TABLE [/BIC/ARTD00111];
[/BIC/ARTD00012__191fe2f9-feee-3887-b13e-eb72e603]:
LOAD
…
APPLYMAP( '__cityKey2GeoPoint', APPLYMAP( '__cityName2Key', LOWER([STANDORT_CONTROLLING])), '-') AS [STANDORT]
RESIDENT [/BIC/ARTD00012_];
DROP TABLE [/BIC/ARTD00012_];
RENAME TABLE [/BIC/ARTD00012__191fe2f9-feee-3887-b13e-eb72e603] to [/BIC/ARTD00012_];
OUTER JOIN ([/BIC/ARTD00012_]) LOAD
FROM [lib://AttachedFiles/test.xlsx]
(ooxml, embedded labels, table is Upload_final);
[/BIC/ARTD00012__f34872a1-bfc8-2e6a-d654-cf03fbdd]:
LOAD
...
APPLYMAP( '__cityKey2GeoPoint', APPLYMAP( '__cityName2Key', LOWER([STANDORT])), '-') AS [/BIC/ARTD00012.STANDORT_CONTROLLING_GeoInfo_1]
RESIDENT [/BIC/ARTD00012_];
DROP TABLE [/BIC/ARTD00012_];
RENAME TABLE [/BIC/ARTD00012__f34872a1-bfc8-2e6a-d654-cf03fbdd] to [/BIC/ARTD00012_];
RENAME TABLE [/BIC/ARTD00012_] to [/BIC/ARTD00012];
TAG FIELD [STANDORT] WITH '$geoname', '$relates_STANDORT _GeoInfo';
TAG FIELD [STANDORT _GeoInfo] WITH '$geopoint', '$hidden', '$relates_CONTROLLING';
TAG FIELD [STANDORT] WITH '$geoname', '$relates_/BIC/ARTD00012.STANDORT _GeoInfo_1';
TAG FIELD [/BIC/ARTD00012.STANDORT _GeoInfo_1] WITH '$geopoint', '$hidden', '$relates_STANDORT_';RENAME TABLE [/BIC/ARTD00012] to [Mastertable];
DROP TABLES __cityAliasesBase, __cityGeoBase;
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;
DERIVE FIELDS FROM FIELDS [DATE0], [/BIC/CREADAT], [/BIC/CHGDAT] USING [autoCalendar] ;
"
But i still get an error:
