Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
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;

2 Replies
thomas_skariah
Creator III
Creator III

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

Hi Tom,

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

Best,

John