4 Replies Latest reply: Jan 5, 2016 11:42 AM by Ruben Marin RSS

    Link Table and Concatenate - Question

      Hi All,

       

      Question:

      I have 2 source files containing different fields and I have created a table with CONCATENATE

       

       

      Table1:

      "Account Name",
      "Platform Name",

      "Ad Name",
      "Budget",

      "ddate"

      THIS IS THE FIRST SOURCE FILE

       

      Concatenate(Table1)

       

      "Campaign Name",
      "Advert set name"
      "Ad Name",

      "ddate",

      THIS  IS THE SECOND SOURCE FILE

       

      I have to link this table to another table, named Table2 (THIRD SOURCE FILE)

       

      What I want to do is to create a Link Table
      so I want to reload Table1 and create a key field for the Link Table, by taking 1 parameter (Account Name) from the first source file and one parameter (Campaign Name) from the second source file and doing something like this.

       

      "Account Name"&'|'&"Campaign Name" as [%key field]


      The reason is that in the THIRD SOURCE FILE, I have the same 2 parameters and that's why I wanted to create a link table

       

       

      Is it possible to do it?
      If not, can you suggest me another workaround so I can work on it?


      Best,

       

      Paolo

        • Re: Link Table and Concatenate - Question
          AC BC

          Hi Paolo,

          You can also use autonumber(your key) for performance improvement

           

          Thanks

          Kiran Kumar

          • Re: Link Table and Concatenate - Question
            Ruben Marin

            Hi Paolo, as a workaround, you can avoid the concatenation in Table1, as none of the rows will have a value in both fields (The rows with value in "Account Name" will have a Null() in "Campaign Name" and vice versa)

             

            Your link table can be loaded with the key field and with both fields 'alone' so each one links to one of the other tables (Accounts and Campaigns), this will return the linked data between an Account and their campaigns avoiding duplicated 'budget'.

             

            Linktable:

            LOAD "Account Name"&'|'&"Campaign Name" as [%key field], // Not really needed?

            "Account Name",

            "Campaign Name"

            FROM ...

             

            It depends on your complete data model if this can be an option.

              • Re: Link Table and Concatenate - Question

                Hi Ruben,

                 

                Thanks for your reply.
                The thing is that I need to do the concatenation since the two tables (which are actually 5 tables) have already 1 field in common (I didn't write it since I wanted to present a simplified version of the problem)

                 

                so what I want to do is to reload my big table (Table 1, which is the concatenation of 5 source files), and create a synthetic key with these 2 fields coming from 2 different source files

                 

                 

                I just wrote something like

                 

                LOAD

                "Account Name"&'|'&"Campaign Name" as [%key field],

                "Account Name",

                "Campaign Name"


                //FROM ...(I didn't write from... since the fields I want to load refers to different source files)

                 

                Resident [Table1];

                 

                But it doesn't work...

                 

                Any idea?

                 

                 

                Thanks

                  • Re: Link Table and Concatenate - Question
                    Ruben Marin

                    Hi Paolo, sorry but I still don't have a mental image of what you're trying and i'm close to be in busy-mode so I won't ask for a sample.

                     

                    As a try of giving a hint that helps you to find a solution...

                    Can you separate the budget in another table?

                     

                    Budgets:

                    LOAD "Account Name"&'|'&"Ad Name" as BgtKey,

                         budget

                    FROM/Resident/...

                     

                    Then you can join the rest of the data wich seems have no problems with duplications (duplicated budget could be a problem if you want to sum() or avg() the values)

                     

                    Table1:

                    LOAD "Account Name"&'|'&"Ad Name" as BgtKey,

                    "Account Name",
                    "Platform Name",

                    "Ad Name",

                    "ddate"

                     

                    [Left/Inner/Outer] Join(Table1)

                    "Campaign Name",
                    "Advert set name"
                    "Ad Name",

                    "ddate",

                     

                    It will return a table wich rows will have a value in Account Name and Campaign Name, from that table you can create your linked key.

                     

                    Still have a field in both table (ddate) with different possibities:

                    - It should be the same date in both dates so it's ok to leave in the join as it is.

                    - It will be different: You can move the ddate from Accounts to the budget table or rename it.

                     

                    Sorry for not giving more help, hope this helps you enough.