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!

       

       

      CampaignMap:

      Mapping Load

      Campaign.CampaignName,

      Campaign.CampaignID

      RESIDENT Campaign;

       

       

      SiteMap:

      Mapping Load

      Site.SiteName,

      Site.SiteID

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

       

       

      Concatenate(Campaign)

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

       

       

       

       

      Concatenate(FactPlanned)

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

       

       

       

       

      Concatenate(Fact)

      LOAD

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

      ExcelDate                                                                                                     AS Calendar.Date,

      ExcelCost                                                                                                              AS Fact.ExcelCost

      RESIDENT Excel;

       

       

      DROP TABLE Excel;