Pivot Table with more than one table source

[#1]

Hi, I need to create a Pivot Table with information comming from 2 separate tables. May be if i just tell exactly what i need its easier.

In one table i hae information from costs of all my marketing campaings, to simplify lets say google and facebook, for every day. This leaves me with one table with one line for each combination od Date and Campaigns. For example:

DATE  Campaign  Cost

1/1       FB               10

1/1       Go               15

2/1       FB              11

2/1       Go                12

In the other i have information from all my transactions wich have assignated part for the google campaign and part to the facebook, as the person could have clicked in both campaigns. This leaves me with one table with one line for each combination of Date, Campaign and TransactionId (tid). For example:

Date   Campaign   Tid   Revenue

1/1      FB             1       2

1/1      FB             2       2

1/1      Go             1       2

1/1      Go             2       2

2/1      FB             3       2

2/1      FB             4       2

2/1      Go             3       2

2/1      Go             4       2

As in transaction 1 we sell a product for 4$ half go to campaing FB and half for Go.

This is why I think I can't do a Join here because we have more lines with the value for a field in one table than in other.

At the end I have one table named Tansactions and another table named Costs that both share a field named Date and another named Campaign. With this i want to create a pivot table to see the sum of cost and the sum of revenue generated by each campaign every day.

I did everything as i described and i get campaigns with cost and campaigns with revenue but not both values together.

Can anybody help me with this??

[marcus_sommer · Accepted solution]

One way could be to create a key from Campaign and Date and associate both tables within the datamodel.

Another approach might be to create a mapping table (with the key of Campaign and Date) from your Cost table and the using applymap() within your transaction table to get the cost-value. Furher you will need a Revenue counter to this key to get a division-factor for the cost-value (you will need another load-step for this).

- Marcus

[#3]

Hey Marcus, thanks for the answer.

I don't quite catch what you mean. May be you could help me explaining it a little easier as I am not that good with Qlikview.

Thanks!

Nico

[marcus_sommer]

The first mentioned way with the associated tables would be:

Cost:

load DATE & '|' & Campaign as DateCampaignKey, Cost from CostTable;

Transaction:

load Date, Campaign, Tid, Revenue, DATE & '|' & Campaign as DateCampaignKey from Transactions;

The second approach needs a few steps more but keeps it in one table:

MapCost:

mapping load DATE & '|' & Campaign as DateCampaignKey, Cost from CostTable;

TransactionTemp:

load

     Date, Campaign, Tid, Revenue,

     applymap('MapCost', DATE & '|' & Campaign, '#NV') as Cost from Transaction;

left join (TransactionTemp)

load

     Date, Campaign, count(Revenue) as RevenueCount resident TransactionTemp;

Transaction:

noconcatenate load

     Date, Campaign, Tid, Revenue, Cost / RevenueCount as Cost

resident TransactionTemp;

drop tables TransactionTemp;

and it will be depend on your requirements how you really handled the Cost and RevenueCount - like in example above by dividing them or if you takes it within the gui.

As you could see the first approach is much easier and should work well in many cases and is the native qlikview-way of connecting (associate) data respectively tables. If this didn't fullfill your requirements you could of course apply various transformations (like in the second approach) to get those data-structures which you need.

- Marcus