topic create 2 tables from one in QlikView

create 2 tables from one

garyk22 — Sat, 16 Nov 2024 17:33:16 GMT

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

Re: create 2 tables from one

edwin — Fri, 29 Jan 2021 15:28:29 GMT

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

Re: create 2 tables from one

edwin — Fri, 29 Jan 2021 15:30:12 GMT

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

Re: create 2 tables from one

garyk22 — Fri, 29 Jan 2021 18:17:46 GMT

@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

Re: create 2 tables from one

edwin — Fri, 29 Jan 2021 18:47:58 GMT

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;

Re: create 2 tables from one

edwin — Fri, 29 Jan 2021 18:55:02 GMT

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;