Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Add additional fields to table for SAP Report

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?

Labels (3)
1 Solution

Accepted Solutions
Highlighted
Contributor II
Contributor II

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

View solution in original post

1 Reply
Highlighted
Contributor II
Contributor II

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

View solution in original post