Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rohitians
Creator III
Creator III

Linked Dimensions Code help

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 NoSanction Amount Disburse AmountLinked Agreement NoNew Sanction AmountNew Disburse Amount
100100000100000102300000300000
102100000100000103
103100000100000
104100000100000105200000200000
105100000100000

Thanks and Regards,

Rohit Yadav

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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
100100000100000102300000300000300000300000
102100000100000103  
103100000100000  
104100000100000105200000200000200000200000
105100000100000

View solution in original post

10 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
swuehl
MVP
MVP

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
100100000100000102300000300000300000300000
102100000100000103  
103100000100000  
104100000100000105200000200000200000200000
105100000100000
rohitians
Creator III
Creator III
Author

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
MVP
MVP

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
Creator III
Creator III
Author

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 NoProductSanction Disburse Linked LanNew SanctionNew Disburse
ABC123BT Transfer100000100000
ABC145BT Top Up100000100000ABC123
ABC789Additional Loan1000010000ABC145210000210000

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
Creator III
Creator III
Author

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
Partner - Champion III
Partner - Champion III

>>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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rohitians
Creator III
Creator III
Author

Dear Jonathan,

Attaching you the qvw file for your reference,

sorry for inconvinience..!

Request you to help..

Thanks and Regards,

Rohit