10 Replies Latest reply: Dec 12, 2017 9:04 PM by Stanley Cheong RSS

    Using Applymap to multiple excel files

    Stanley Cheong

      Hi, i'm looking to map columns from one excel file to multiple excel files in a 'For Loop' using the Applymap() function. Is there an example of how this could be achieved?

       

      E.g:

      Multiple Mapping.PNG

       

      Objective End Result:

      EndResult.PNG



      My code now for the Reports is something like:

      ------------------------------------------------

      FOR Each File1 in filelist ('lib://ExcelFiles/1 Templates\Report 2017*.xlsx')

       

      SumReportOct:

       

        LOAD

        "ExtractedValue",

        "ReturnedClusterName"

        "Compliance Score"

       

      FROM '$(File1)' (ooxml, embedded labels, table is [Overall_Score]);

      NEXT;

      ------------------------------------------------

       

       

       

      My code now for the Asset List is something like:

      ------------------------------------------------

       

      ClusterToClusterMapping:

      Mapping Load

      "ReturnedClusterName",

      "Compliance Score"

      Resident SumReportOct;

       

      AssetListComplianceScoreHostZ:

      Mapping Load Distinct

      "Extractedvalue",

      "Compliance Score"

      Resident SumReportOct;

       

      AssetList1:

       

      load*,

      ApplyMap('AssetListComplianceScoreHostZ',"HostNameFinal",ApplyMap('AssetListComplianceScoreHostZ',"Cluster Name"

      ,ApplyMap('ClusterToClusterMapping',"Cluster Name",'Unknown'))) as "Compliance Score Final"

      ;

       

      LOAD

      "HostNameFinal" as "HostNameFinal"

      "Cluster Name" as "Cluster Name"


      FROM [lib://ExcelFiles/1 Templates\Asset Data October 2017.xlsx](ooxml, embedded labels, table is Server);

       

      ------------------------------------------------