topic Re: How to Identify vendor based on campaign Name? in QlikView

How to Identify vendor based on campaign Name?

Tue, 07 Jan 2014 12:04:23 GMT

Hi Everyone,

My first post here so don't be too harsh.

I have uploaded 2 excel sheets to Qlikview. In one sheet I have a column called "Campaign Name" which basically has names of different campaigns and their clicks. In another sheet i have the names of Vendors along with the spends for those Vendors. Multiple "Campaign names" can have a single vendor, but no campaign name will have more than one vendor. So I basically want to add another column in the first sheet which basically does a vlookup of the campaign name with the vendor name. What is the best way to do this? Would i need to add another excel with just campaign names and vendors which forms a kind of reference list (like vlookup?)

Thanks for all the replies beforehand!

Re: How to Identify vendor based on campaign Name?

nilesh_gangurde — Tue, 07 Jan 2014 12:08:13 GMT

Hi,

Excel sheets are not attached here.

just do left join in qlikview, this will serve the purpose.

-Nilesh

Re: How to Identify vendor based on campaign Name?

Tue, 07 Jan 2014 12:11:23 GMT

Nilesh Gangurde wrote:

Hi,

Excel sheets are not attached here.

just do left join in qlikview, this will serve the purpose.

-Nilesh

Hi Nilesh,

Thanks for the response, but can you elaborate on it a bit? should I attach the actual excels?

Re: How to Identify vendor based on campaign Name?

nilesh_gangurde — Tue, 07 Jan 2014 12:13:47 GMT

If posible please attach the sample data.

-Nilesh

Re: How to Identify vendor based on campaign Name?

Anonymous — Tue, 07 Jan 2014 12:18:26 GMT

Use

Table 1:

Load

Campaign,

Clicks

from Campaign_master.xls;

Table 2:

Load Campaign, Vendor,Spends

fro Vendor;

Create a chart may be straight table

with dimensions as Campaign, Vendor

and expression as sum(clicks) and sum(spends).

Hope this helps,

If I have not understood your requirement, please provide the excel files with sample data , not original data and what is your output required.

Regards

Nitin

Re: How to Identify vendor based on campaign Name?

MK_QSL — Tue, 07 Jan 2014 12:21:24 GMT

Hope this will help you...

Re: Re: How to Identify vendor based on campaign Name?

Tue, 07 Jan 2014 12:41:04 GMT

Manish Kachhia wrote:

As far as there is a common field between them, there is no need to do join... it will automatically do natural join

Please check enclosed document which might help you...

if you are unable to open due to having personal edition of qlikview....

do something as below script

Load * Inline

[

Campaign Name, Vendor

CN01, Ven01

CN02, Ven01

CN03, Ven02

CN04, Ven03

CN05, Ven04

CN06, Ven04

CN07, Ven05

];

Load * Inline

[

Vendor, VendorName

Ven01, Vendor 01

Ven02, Vendor 02

Ven03, Vendor 03

Ven04, Vendor 04

Ven05, Vendor 05

];

Hi Manish and everyone else!

Super Happy to see such helpful responses!

Attached are the 3 excels. As you can see, there is no direct field which is common between the campaign and the spends sheet , so attached another excel for the reference sheet. This sheet is available on a daily basis so any solution which incorporates this will probably be best. I dont want to have a direct vendor detail code written in the script as sometimes the campaigns are >200 with new campaigns being added everyday, changing the script everyday would not be optimal. Also to be kept in mind is <Null> where the clicks have come through none of the vendors, how to take care of that possibility in the script.

Thanks for the responses in advance!