topic Re: Linked Dimensions Code help in QlikView

Linked Dimensions Code help

rohitians — Fri, 10 Jun 2016 09:31:46 GMT

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

Re: Linked Dimensions Code help

jonathandienst — Fri, 10 Jun 2016 09:41:39 GMT

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.

Re: Linked Dimensions Code help

swuehl — Fri, 10 Jun 2016 10:14:36 GMT

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

Re: Linked Dimensions Code help

rohitians — Fri, 10 Jun 2016 13:01:40 GMT

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

Re: Linked Dimensions Code help

swuehl — Fri, 10 Jun 2016 13:12:32 GMT

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?

Re: Linked Dimensions Code help

rohitians — Mon, 13 Jun 2016 04:46:55 GMT

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

Re: Linked Dimensions Code help

Digvijay_Singh — Mon, 13 Jun 2016 05:05:30 GMT

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;

Re: Linked Dimensions Code help

rohitians — Mon, 13 Jun 2016 06:08:04 GMT

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

Re: Linked Dimensions Code help

jonathandienst — Mon, 13 Jun 2016 06:26:19 GMT

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

Re: Linked Dimensions Code help

rohitians — Mon, 13 Jun 2016 07:12:06 GMT

Dear Jonathan,

Attaching you the qvw file for your reference,

sorry for inconvinience..!

Request you to help..

Thanks and Regards,

Rohit

Re: Linked Dimensions Code help

jonathandienst — Mon, 13 Jun 2016 08:29:31 GMT

Can you give some indication of what you expected? It looks OK to me but I don't know what values to expect.

It would also be helpful if you could upload the source QVD.