Add additional fields to table for SAP Report

[JoshW]

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?

[JoshW · Accepted solution]

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);