Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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;

Tags (2)
2 Replies
thomas_skariah
Contributor III

Re: Help with Concatenate

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

Not applicable

Re: Help with Concatenate

Hi Tom,

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

Best,

John

Community Browser