Use of key to join tables on client and date field?

[stevelord]

Hi, I have many tables (phone, email and chat logs) that I want to link on both client name and the date field with a key function/link table thing rather than let it ride on a synthetic key.

Can someone provide the correct syntax for that?

In script the tables look about like this.

LOAD ClientAccountName,

     [Phone Call Count],

     Date

From Wherever;

LOAD ClientAccountName,

     [Email Count],

     Date

From Wherever;

LOAD ClientAccountName,

     [Chats Started],

     Date

From Wherever;

A synthetic key would form around client account name and date and I'm probably safe with that, but I just have an aversion to passive synthetic key stuff and would like to lock in a key around clientaccountname&date.  This is just for a qlikview looking at a few excel worksheets where data is manually placed while we develop a more automated solution.  I will browse around and try to self-educate, but thought I'd put this up while I browse around.  Thanks in advance!

[Sokkorn]

Hi Steve,

You may try combine [ClientAccountName] and [Date] field as link key. Maybe like this

[Table1]:

Load

     Floor([Date]) & [ClientAccountName]     AS [_Key],

     [ClientAccountName],

     [Phone Call Count]

From Table1;

[Table2]:

Load

     Floor([Date]) & [ClientAccountName]     AS [_Key],

     [Email Count]

From Table2;

.......

Regards,

Sokkorn

[jagan · Accepted solution]

Hi,

Try like this

PhoneData:

LOAD

     ClientAccountName & '_' & Date AS Key,

     ClientAccountName AS PhoneClientAccountName ,

     [Phone Call Count],

     Date AS PhoneDate

From Wherever;

EmailData:

LOAD

     ClientAccountName & '_' & Date AS Key,

     ClientAccountName AS EmailClientAccountName ,

     [Email Count],

     Date AS EmailDate

From Wherever;

ChatData:

LOAD

     ClientAccountName & '_' & Date AS Key,

     ClientAccountName AS ChatClientAccountName ,

     [Chats Started],

     Date AS ChatDate

From Wherever;

or else you just concatenate all the 3 tables

Data:

LOAD ClientAccountName,

     [Phone Call Count],

     Date,

     'Phone' AS DataType

From Wherever;

Concatenate(Data)

LOAD ClientAccountName,

     [Email Count],

     Date,

     'Email' AS DataType

From Wherever;

Concatenate(Data)

LOAD ClientAccountName,

     [Chats Started],

     Date,

     'Chat' AS DataType

From Wherever;

Regards,

Jagan.