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;
