Forum Discussion

danielco's avatar
danielco
Regular Visitor
8 years ago

Filter like in Pivot

hi guys,   After a few hours of research i need to ask this as i can't seem to find it   i have two excels  first with a campaign name website and some target indicators   second has many camp...
danielco's avatar
danielco
Regular Visitor
8 years ago

Hi Ian,

 

I've made a replica of my files 

Please see below the .pbx

https://we.tl/LRNVancOtx

 

Most likely is something that i am missing

Or my approach is wrong

 

In the above file. when filtering for FAR FIR campaign, Ringier RO should have 400 impressions

 

thanks

IanSwanepoel's avatar
IanSwanepoel
Helper I
8 years ago

Hi Daniel,

Had a look. See that the campaigns across the 2 tables are different, even for the same sites.
Eg in 6539_Untitled aerotravel.ro is connected to both 201612 ASD DFA as well as 201611 FAR FIR, whereas in sheet 1 aerotravel.ro is only connected to 201611 FAR FIR.

The filter you applied appears to be from 6539_Untitled so it pulls through the value based on the affected websites(for relationship). So filtering on FAR FIR will pull through ALL the values connected to aerotravel.ro, which includes the values for ASD DFA, because there is no direct relationship between the Campaign field..
This results in the impressions including values for the other campaigns. However when filtering on Campaign directly from Sheet 1 you get the following: Which has the 400 as specified in your post.


Hope this helps.

P.S. In regards to the fact that a relationship cannot be established because there are multiple values in both tables. You can make a bridging table. This is how I generally solve the issue of many to many relationships.

As displayed above I created a custom table which contains only the unique values for Campaign (now there are no duplicates)
So you can connect it to both tables and create an indirect relationship between the 2. (This is also popularly refered to as a FACT table)

  • danielco's avatar
    danielco
    Regular Visitor
    8 years ago

    thanks a lot for your time

     

    I'll take a look

     

    Just to give you some background:

    the  6539_Untitled file usually contains a few thousand lines. With many campaigns, each website can appear in as many campaigns as possible. and i have 2 more files like this.

     

    I want to use Sheet 1 table to extract all data related to 1 campaign in all tables available.

     

    I need to be able to change what is in Sheet 1 (campaign name) at any point and therefore get reports for as many campaigns as needed

     

    Thanks again

    • IanSwanepoel's avatar
      IanSwanepoel
      Helper I
      8 years ago

      Then my reccomendation would be to concatinate the Website and Campaign field to create a key, seeing as it is based on both.
      Then using that value to establish the relationship.