Linked Dimensions Code help

[rohitians]

Dear Users,

I have a following scenario

wheres in one agreement number is linked to some other agreement number,

every agreement has a sanction and disburse  amount  but  where ever a linked agreement exist the sanction and disburse amount changes because the new amount is total of all link agreements as shown below.

Is it possible??

Request your help..

Agreement No	Sanction Amount	Disburse Amount	Linked Agreement No	New Sanction Amount	New Disburse Amount
100	100000	100000	102	300000	300000
102	100000	100000	103
103	100000	100000
104	100000	100000	105	200000	200000
105	100000	100000

Thanks and Regards,

Rohit Yadav

[jonathandienst]

Not sure hat you are asking.

>>Is it possible??

Probably, but as i am not sure what you are asking, its hard to say.

Presumably, you want to combine or consolidate the amounts by the linked agreements. For which agreement would you report the consolidated amounts? You might want to consider building a hierarchy of links as they appear to be on more than one level. Some adaptation of Hierarchy and/or HierarchyBelongsTo loads may help.

[swuehl · Accepted solution]

HIerarchyBelongsTo might help you indeed. Maybe something along these lines

INPUT:
LOAD [Agreement No], 
     [Sanction Amount], 
     [Disburse Amount], 
     [Linked Agreement No], 
     [New Sanction Amount], 
     [New Disburse Amount]
FROM
[https://community.qlik.com/thread/220340]
(html, codepage is 1252, embedded labels, table is @1);


TMPHIERARCHY:
LOAD [Agreement No], 
      [Linked Agreement No]
RESIDENT INPUT;

CONCATENATE 
LOAD
      NULL() as [Agreement No], 
      [Agreement No] as [Linked Agreement No],
      1 as RootFlag
RESIDENT INPUT
WHERE not exists(  [Linked Agreement No],[Agreement No]);


HIERARCHY:
HierarchyBelongsTo(NodeID,ParentID,NodeName,AncestorID,AncestorName)
LOAD [Agreement No] as ParentID,
  [Linked Agreement No] as NodeID,
  [Linked Agreement No] as NodeName
RESIDENT TMPHIERARCHY;


Left JOIN (HIERARCHY)
LOAD [Agreement No] as NodeID,
     [Sanction Amount], 
     [Disburse Amount]
RESIDENT INPUT;


LEFT JOIN (HIERARCHY)
LOAD [Linked Agreement No] as [AncestorID],
  RootFlag
RESIDENT TMPHIERARCHY
WHERE RootFlag;



LEFT JOIN (INPUT)
LOAD AncestorID as [Agreement No],
  Sum( [Sanction Amount]) as NewSanction,
     Sum( [Disburse Amount]) as NewDisburse
Resident HIERARCHY
WHERE RootFlag
Group BY [AncestorID]
;


DROP TABLES HIERARCHY,TMPHIERARCHY;

Agreement No	Disburse Amount	Sanction Amount	Linked Agreement No	New Disburse Amount	New Sanction Amount	NewDisburse	NewSanction
100	100000	100000	102	300000	300000	300000	300000
102	100000	100000	103
103	100000	100000
104	100000	100000	105	200000	200000	200000	200000
105	100000	100000

[rohitians]

Dear Stefan,

I have a agreement master of 50000 records,

its taking more then 40 mins to execute is it normal??

Thanks and Regards,

Rohit Yadav

[swuehl]

Which LOAD statement is the script currently executing? The one with the HierarchyBelongsTo prefix?

Do you have a more complex tree than what we can see above (where there is like a 1:1 parent-child relationship)?

How many levels do you have in your hierarchy?

[rohitians]

Dear Stefan,

Sorry for late reply,

I solution is perfect as suggested by you,

but it is not suitable for this code,

Here is simply want is the code to run in a loop until there is no more link and the sanction and disbursed amount kept on adding.

I will give you a brief example ,

A customer has a loan in a bank currently ,

now he decides to balance transfer the loan to our bank so he does that,

now a loan account is generated suppose.  ABC123 also he wants a TOP up amount on the loan so a new agreement id is generated ABC145  also we convince him to take a insurance for the loan so a new agreement id is created for the same ABC789 .

Agreement No	Product	Sanction	Disburse	Linked Lan	New Sanction	New Disburse
ABC123	BT Transfer	100000	100000
ABC145	BT Top Up	100000	100000	ABC123
ABC789	Additional Loan	10000	10000	ABC145	210000	210000

Now I want to calculate Insurance penetration so i require the total sanction and Disburse amount  so I need to consider ABC789 but for that I will have to consider all links for calculating total sanction and disburse amount now in this case my first link is ABC145 it will add the sanction and disburse  again it will check if any more links here we have ABC123 link for ABC145  and so on after this we get a New Sanction and Disburse Amount.

HIerarchyBelongsTo is not giving desired result as there is no parent Child relation as such.

I just want a loop and go on adding the amount until no links found .

Thanks for your guidance..

[Digvijay_Singh]

Check if below script can help, I am not sure yet how to loop it only for the root element -

T1:

load * inline [

AgreementNo,Product, SanctionAmount, DisburseAmount, LinkedAgreementNo

ABC123, BT Transfer, 100000, 100000,

ABC145, BT Top Up, 100000, 100000, ABC123

ABC789, Additional Loan, 10000, 10000, ABC145

];

T2:

load *,

  if(LinkedAgreementNo=Previous(AgreementNo),SanctionAmount+Peek(NewSanctionAmount),SanctionAmount) as NewSanctionAmount,

  if(LinkedAgreementNo=Previous(AgreementNo),DisburseAmount+Peek(NewDisburseAmount),DisburseAmount) as NewDisburseAmount

  Resident T1;

Drop table T1;

[rohitians]

Which LOAD statement is the script currently executing? The one with the HierarchyBelongsTo prefix?

YES

Do you have a more complex tree than what we can see above (where there is like a 1:1 parent-child relationship)?

NOT so complex

How many levels do you have in your hierarchy?

Max 3-4 level not more...

[jonathandienst]

>>HierarchyBelongsTo is not giving desired result as there is no parent Child relation as such.

I just want a loop and go on adding the amount until no links found.

But does not the linked agreement represent a parent / child relationship? I would expect the HierarchyBelongsTo load to be faster than looping through the data multiple times.

I suggest that you prepare a small, representative sample of your qvw and, if possible, a sample data set in an xls or csv file for further analysis.

Preparing examples for Upload - Reduction and Data Scrambling

[rohitians]

Dear Jonathan,

Attaching you the qvw file for your reference,

sorry for inconvinience..!

Request you to help..

Thanks and Regards,

Rohit