Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We are pulling data through the use of SAP Reports. These are to be used daily to gather data but I'd simply like to attach a date field when loading. Every time I add a field, I get the "mismatch detected" error. Is there another way I can do this?
LET KEYDATE = Date(Today(), 'YYYYMMDD');
Load
[Field1_] as [RDATA],
Date($(KEYDATE)) AS [DATE]
SELECT SAPREPORT (REPORT (Z_3_REPO), ROWS_PER_RECORD(1), FIELD_AUTO_COLUMNS(6),
OPTIONS (
(SELNAME P_FDATE, KIND P, SIGN I, OPTION EQ, LOW $(KEYDATE))
));
What I'd ultimately like to do is write a script that cycles through a set of dates and pull the daily reports and then add those to a table along with the date associated with the report.
One idea was to cycle through a loop and pull data into the table. From there I can concate this data into another table that includes the date from he for loop. Any ideas on how to get this going?
I resolved my issue with the following script. I'm not 100% sure if it is the easiest way but it works. This code will cycle through by a start and end date, create a temporary table from SAP Report connector and then create another temp table adding in the dates specified. Once this is done, I save it all to a QVD file as it is a daily report pull. I hope this helps someone out there.
// STORAGE PATH
LET vQVDPath = 'lib://QVDFiles (user)/';
// RUN THROUGH TODAY
LET KEYDATE = Date(Today(), 'YYYYMMDD');
// START ON THIS DATE
LET STARTDATE = Date('01/01/2020', 'YYYYMMDD');
// LOOP TIME.
LET day = 1;
DO WHILE day < (KEYDATE - STARTDATE)
LET CURRENTDATE = Date(STARTDATE + day, 'YYYYMMDD');
// CONNECT TO SAP REPORT CONNECTOR
LIB CONNECT TO 'SAPReportCon (user)';
// SET ERROR MODE IN THE CASE THAT THE REPORT ISN'T FOUND FOR THIS DATE
// AND ALSO TO IGNORE ERROR FROM LIBRARY NAME
SET ErrorMode = 0;
Set errormode = 0;
TempTable:
Load
[Field0_] AS [RDATA];
Select SAPREPORT (REPORT (ZSD_39_COMMISSION_STMT), ROWS_PER_RECORD (1), FIELD_AUTO_COLUMNS (6),
OPTIONS (
(SELNAME P_FDATE, KIND P, SIGN I, OPTION EQ, LOW $(CURRENTDATE))
));
// CREATE TEMPORARY TABLE WITH DATE ADDED
TempTable2:
NoConcatenate
LOAD RDATA,
$(KEYDATE) as PULLDATE,
$(CURRENTDATE) as REPORTDATE
Resident TempTable;
// Not sure if this is needed but errors thrown if I don't have it.
drop fields PULLDATE, REPORTDATE from TempTable;
// Drop the SQP Report Table
drop table TempTable;
// IF FILE EXISTS, CONCATENATE
if NOT IsNull(QVDCreateTime('$(vQVDPath)RDATA.qvd')) then
Concatenate(TempTable2)
LOAD * FROM [$(vQVDPath)RDATA.qvd](qvd) WHERE [REPORTDATE] <> $(CURRENTDATE);
end if
// STORE FILE
if NoOfRows('TempTable2') > 0 then
STORE TempTable2 INTO [$(vQVDPath)RDATA.qvd](qvd);
end if
drop table TempTable2;
SET ErrorMode = 1;
Set errormode = 1;
// INCREMENT DAY
day = day + 1;
// Sleep performed due to overload on SAP causing errors. Brief pause.
Sleep 500;
LOOP;
// LOAD THE REPORT INTO A TABLE TO USE
[RDATA_REPORT]:
Load * FROM [$(vQVDPath)RDATA.qvd](qvd);
I resolved my issue with the following script. I'm not 100% sure if it is the easiest way but it works. This code will cycle through by a start and end date, create a temporary table from SAP Report connector and then create another temp table adding in the dates specified. Once this is done, I save it all to a QVD file as it is a daily report pull. I hope this helps someone out there.
// STORAGE PATH
LET vQVDPath = 'lib://QVDFiles (user)/';
// RUN THROUGH TODAY
LET KEYDATE = Date(Today(), 'YYYYMMDD');
// START ON THIS DATE
LET STARTDATE = Date('01/01/2020', 'YYYYMMDD');
// LOOP TIME.
LET day = 1;
DO WHILE day < (KEYDATE - STARTDATE)
LET CURRENTDATE = Date(STARTDATE + day, 'YYYYMMDD');
// CONNECT TO SAP REPORT CONNECTOR
LIB CONNECT TO 'SAPReportCon (user)';
// SET ERROR MODE IN THE CASE THAT THE REPORT ISN'T FOUND FOR THIS DATE
// AND ALSO TO IGNORE ERROR FROM LIBRARY NAME
SET ErrorMode = 0;
Set errormode = 0;
TempTable:
Load
[Field0_] AS [RDATA];
Select SAPREPORT (REPORT (ZSD_39_COMMISSION_STMT), ROWS_PER_RECORD (1), FIELD_AUTO_COLUMNS (6),
OPTIONS (
(SELNAME P_FDATE, KIND P, SIGN I, OPTION EQ, LOW $(CURRENTDATE))
));
// CREATE TEMPORARY TABLE WITH DATE ADDED
TempTable2:
NoConcatenate
LOAD RDATA,
$(KEYDATE) as PULLDATE,
$(CURRENTDATE) as REPORTDATE
Resident TempTable;
// Not sure if this is needed but errors thrown if I don't have it.
drop fields PULLDATE, REPORTDATE from TempTable;
// Drop the SQP Report Table
drop table TempTable;
// IF FILE EXISTS, CONCATENATE
if NOT IsNull(QVDCreateTime('$(vQVDPath)RDATA.qvd')) then
Concatenate(TempTable2)
LOAD * FROM [$(vQVDPath)RDATA.qvd](qvd) WHERE [REPORTDATE] <> $(CURRENTDATE);
end if
// STORE FILE
if NoOfRows('TempTable2') > 0 then
STORE TempTable2 INTO [$(vQVDPath)RDATA.qvd](qvd);
end if
drop table TempTable2;
SET ErrorMode = 1;
Set errormode = 1;
// INCREMENT DAY
day = day + 1;
// Sleep performed due to overload on SAP causing errors. Brief pause.
Sleep 500;
LOOP;
// LOAD THE REPORT INTO A TABLE TO USE
[RDATA_REPORT]:
Load * FROM [$(vQVDPath)RDATA.qvd](qvd);