topic Re: Link Table and Concatenate - Question in QlikView

Link Table and Concatenate - Question

Tue, 05 Jan 2016 14:46:16 GMT

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

kkkumar82 — Tue, 05 Jan 2016 15:34:41 GMT

Hi Paolo,

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

Thanks

Kiran Kumar

Re: Link Table and Concatenate - Question

rubenmarin — Tue, 05 Jan 2016 15:37:21 GMT

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

Tue, 05 Jan 2016 15:47:43 GMT

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

rubenmarin — Tue, 05 Jan 2016 16:42:07 GMT

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.