2 Replies Latest reply: Oct 15, 2012 3:52 PM by jmorris6 RSS

    Help with Concatenate

      I am new to Qlikview and looking for a little bit of help concatenating in some data. I have been working off a script that was left to me and it is a little over my head...


      I am trying to bring in new data from an excel file and have that data combine with data already in Qlikview. For example:

      Excel File has: Date, Campaign, Brand, Site, Cost


      and try to bring that data into two different already existing charts:

      Chart 1: Date, Brand, Campaign, Cost

      Chart 2: Date, Site, Campaign, Cost


      I have been trying to concatenate this data in, however it brings up table errors or seems to bring in the data incorrectly.


      The script I have been using is below, I have used "Excel" as an example. Other things to note, both Site and Campaign data already in Qlikview have IDs that are not in the excel file. Please give it a look and any help is greatly appreciated!




      Mapping Load



      RESIDENT Campaign;




      Mapping Load



      RESIDENT Site;


      Concatenate (Site)

      Load Distinct

      ApplyMap('SiteMap',ExcelSite,AutoNumber(ExcelSite))                    AS Site.SiteID,

      ExcelSite                              AS Site.SiteName

      Resident Excel

      WHERE NOT Exists(Site.SiteID,ApplyMap('SiteMap',ExcelSite,AutoNumber(ExcelSite))) ;




      Load Distinct

      ApplyMap('CampaignMap',ExcelCampaign,AutoNumber(ExcelCampaign))                    AS Campaign.CampaignID,

      ExcelCampaign                              AS Campaign.CampaignName,

      ExcelBrand                    AS Campaign.Brand

      Resident Excel

      Where NOT Exists(Campaign.CampaignID,ApplyMap('CampaignMap',ExcelCampaign,AutoNumber(ExcelCampaign)));






      LOAD Distinct

      AutoNumber(ApplyMap('CampaignMap',ExcelCampaign) & '_' & ApplyMap('SiteMap',ExcelSite))*1000            AS Fact.FactPlannedKey,

      ApplyMap('SiteMap',ExcelSite)                     AS Site.SiteID,

      ApplyMap('CampaignMap',ExcelCampaign)           AS Campaign.CampaignID

      RESIDENT Excel

      Where Not Exists(Fact.FactPlannedKey,AutoNumber(ApplyMap('CampaignMap',ExcelCampaign) & '_' & ApplyMap('SiteMap',ExcelSite))*1000);







      AutoNumber(ApplyMap('CampaignMap',ExcelCampaign) & '_' & ApplyMap('SiteMap',ExcelSite))*1000            AS Fact.FactPlannedKey,

      ExcelDate                                                                                                     AS Calendar.Date,

      ExcelCost                                                                                                              AS Fact.ExcelCost

      RESIDENT Excel;



      DROP TABLE Excel;