create 2 tables from one

[garyk22]

HI,

I have 2 tables one which has call data and another one with parent and child relationship. Trying to split parent and child but want to keep call details for both.

Table: Accounts

Parent ID
Parent Name

Child ID

ChildName

Table: CallDetails 

Account ID

Call ID
Call Notes

The "AccountID "in call table shows calls for both child and parent so how can i separate this I think creating two table out of account table is best way to do it so I need to split child and parent but still maintain the relationship between child and parent to be able to create a network chart.

Note: I would like to create a link attribute for parent and child if possible when I am splitting the table 

Thank you

[edwin]

if i understand it correctly, two fields in Account needs to be linked to one field in details

there will be a few ways to solve this and each will be correct - it will just depend on the development style of whoever is doing it and optimization considerations.

one way is to do a bridge:
in accounts- create a field which is concat of Parent and Child (ParentID & '|' & ChildID) at the start leave it as string

then create a temptable:
load distinct of teh new field + Parent ID and Child ID
create the bridge:
load distinct of Key, ParentID as AccountID, 'PARENT' as datatype
concatenate load distinct of Key, ChildID as AccountID, 'CHILD' as datatype

so if you need to analyze parents, just add datatype={'PARENT'} in set analysis..
if you just need straight list of all accounts + details regardless of parent/child, just use accountID and the details.  it will automatically link the respective accounts.

even if a single accountID is linked to both parent and child, at the end of the day QV will sum only what is in the details (assuming that is where your measures are)  to illustrate:

details:

Account              Measure
Acct1                    100

bridge: 

Account               Key (Parent Child)                      Datatype
Acct1                     Acct1 | Acct2                                  PARENT
Acct1                     Acct3|Acct1                                    CHILD

ParentChild:

Parent          Child
Acct1            Acct2
Acct3            Acct1

if through SQL, this would duplicate the entries for ACCT1 possibly giving you 200 as sum(measure)
but QV doesnt care how many records is created to get to your measure it doesnt double count:

Account    Sum(Measure)
Acct1         100

[edwin]

once youve finalized this, you can use autonumber on the new field to optimize / save on memory

it is easier to test if you can see the content of your fields

[garyk22]

@edwin 

Thank you for your response. In a way it's correct that there are call details in the second table. It's hard for me to follow what you wrote because I am very new to this. Please review below script and suggest me changes as it gives me an error "Key" not found

LOAD PARENTID,
ChildID ,
Child_Name ,
Parent_Name,

Parent_Account_vod__c&'|'&Child_Account_vod__c as IDs
;

SELECT PARENTID,
ChildID ,
Child_Name ,
Parent_Name,
FROM Child_Account;

Temp:
Load Distinct Parent_Account_vod__c&'|'&Child_Account_vod__c as Key
;

Bridge:

Load distinct
Key,

Parent_Account_vod__c as AccountID
;

Concatenate

Load Distinct
Key,

Child_Account_vod__c as AccountID;

LOAD Name as CallID,
AccountID,
Call_Date,
Detailed_Products,,
Year(Call_Date_) as Year
;

SELECT Name,
AccountID,
Call_Date,
Detailed_Products
FROM Call2_Details;

both child account and parent account had calls and I am trying to get call details for both by connecting them to call_details ID

[edwin]

this is the part that creates the bridge:

RELATIONSHIP:
LOAD PARENTID,
ChildID,
PARENTID &'|'& ChildID as IDs
inline [   //you replace this section with your load and add what fields you need
PARENTID,ChildID
ACCT1, ACCT2
ACCT1, ACCT3
ACCT4,ACCT5
ACCT0, ACCT1
ACCT0, ACCT4
];

NoConcatenate
Temp:
Load Distinct IDS, PARENTID,ChildID
resident RELATIONSHIP
;

NoConcatenate
Bridge:

Load distinct
IDs,
PARENTID as AccountID,
'PARENT' as DataType
;

Concatenate (Bridge)
Load distinct
IDs,
ChildID as AccountID,
'CHILD' as DataType
;

drop table Temp;

[edwin]

this one i tested:

RELATIONSHIP:
LOAD PARENTID,
ChildID,
PARENTID &'|'& ChildID as IDs
inline [
PARENTID,ChildID
ACCT1, ACCT2
ACCT1, ACCT3
ACCT4,ACCT5
ACCT0, ACCT1
ACCT0, ACCT4
];

NoConcatenate
Temp:
Load Distinct  IDs, PARENTID,ChildID
resident RELATIONSHIP
;

NoConcatenate
Bridge:
Load distinct
IDs,
PARENTID as AccountID,
'PARENT' as DataType
resident Temp
;

Concatenate (Bridge)
Load distinct
IDs,
ChildID as AccountID,
'CHILD' as DataType
;

drop table Temp;