0 Replies Latest reply: Feb 28, 2017 2:13 PM by Madhur Mehrotra RSS

    Error in Loading Data from Url stored in an excel column

    Madhur Mehrotra

      I am trying to load some employee id from different report urls for my usage dasboard stored is an excel. My data set is

        

      Report name URLs
      RPT1Link1
      RPT1Link2
      RPT1Link3
      RPT2Link1
      RPT2Link2
      RPT2Link3
      RPT3Link4

      Now  I am trying to capture URLs value in a variable and use that variable as web service to retrieve employee id in that link

      script is

      Report_URL:

      LOAD UPPER(Report_name) as Dashboard,

           [URLs]

      FROM

      [a.xls]

      (ooxml, embedded labels, table is Sheet1);

       

      LET rowText = NoOfRows('Report_URL');

       

      for i=1 to $(rowText) // loop through every row

       

                let URL = FieldValue('URL',$(i));

                 let vDashboard = FieldValue('Dashboard',$(i));

                

      OtherAppUSERS:

       

      LOAD //RowNo() as %ENTTL_ID,

            [@13:19],

           '$(vDashboard)' as [Doc Name]

      FROM

      $(URL) (fix, codepage is 1252)

      WHERE [@1:12] = '<EmployeeID>';

      next

       

       

      This is giving me some irrelevant error

      The system cannot find the file specified.

       

      OtherAppUSERS:

       

      LOAD

            [@13:19],

           'RPT2' as [Doc Name]

      FROM

      (fix, codepage is 1252)

      WHERE [@1:12] = '<StandardId>'

       

      I guess repeated link is issue here but not sure how can i resolve this. I can create different QVD n concatenate but want to resolve this error so that if any new report comes in we dont have to change the code and can update the sheet itself