Help with Concatenate

Sun, 14 Oct 2012 22:16:50 GMT

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;

Re: Help with Concatenate

thomas_skariah — Mon, 15 Oct 2012 01:27:21 GMT

Hi,

You didn't mentioned the base file where the data is actually coming,also may i know the error that has been displayed.

Regards,

Tom

Re: Help with Concatenate

Mon, 15 Oct 2012 19:52:10 GMT

Hi Tom,

Actually a colleague of mine was able to sort out the script for me, however thank you for reaching out!

Best,

John

topic Re: Help with Concatenate in QlikView

Help with Concatenate

Re: Help with Concatenate

Re: Help with Concatenate