Efficient way to load data

[jduluc12]

Hi,

Here is my data set.

temp1:
LOAD * INLINE [
Cust, Type, Amt, Num
1, SO, 50, 1
1, SR, 25, 2
1, INV, 25, 3
1, PMT, 25, 4
2, SO, 60, 10
2, SO, 80, 11
2, SR, 30, 13
2, SR, 40, 20
2, INV, 30, 22
2, INV, 40, 45
2, PMT, 30, 34
2, PMT, 20, 56
2, PMT, 20, 58
];

Link:
load * Inline [
NUM1, NUM2, LINKNAME, AMT
1,3, SOINV, 25
10, 22, SOINV, 30
3,4, INVPMT, 25
22,34, INVPMT, 30,
45,56,INVPMT,20,
45,58,INVPMT,20,
11,45,SOINV,40,
];

I have to create a chart where all temp1.type for each temp1.cust should appear together.  In other words, I have to build a relationship between transactions for one Cust.

For example:

The SO and SR are related to each other on the basis of amount. The amount of SR should be half of the SO for them to be related with each other.

The SO and INV are related based on rows in the Link table. The Link.NUM1 is the Num for SO and the Link.NUM2 is the Num for INV and the Link.LINKNAME is SOINV.

The INV and PMT are also related with each other based on the rows in the Link table. The Link.NUM1 is the Num for INV and the Link.NUM2 is the Num for PMT and the Link.LINKNAME is INVPMT.

One INV can have many PMT.

I have got it working and i am attaching the file. I think there has to be a better way than what am I doing.

My output looks like this, which is a correct output.

[derekjones]

Hi

I think I understand your issue, you have a nodal relationship on the 4 types of transaction, so using the hierarchy function to split out nodes you can then combine again to find the parent node for each transaction  batch. I've added my results attached, which is slightly neater, however it has questioned your results e.g. Num 13 in your dataset has no relation in your link table so my parent is itself i.e. no link to other documents, yet in your results 13 is linked to final.relation =2. Is there a piece of logic I'm missing on 'orphaned' records?

[jduluc12]

Thanks for looking at it.

somehow  I could not upload the qvw file. would you mind attaching a text file?

Now about the record 13.

It is showing up correctly in my report. The SO and SR are not linked via the Link table.  We have to find the link between SO and SR by looking at their amount.

The amount of the SR must be half of the SO.

This part of my code (in red ) handles that

for vSRRow = 0 to (NoOfRows('SR')-1)
let vSRAmt = Peek('SR.temp1.Amt', vSRRow, 'SR');
let vSRNum = Peek('SR.temp1.Num', vSRRow, 'SR');
IF vSOAmt = vSRAmt * 2 then
Concatenate(Final)
Final_$(vSRRow):
LOAD
SR.temp1.Cust as Final.Cust,
SR.temp1.Type as Final.Type,
SR.temp1.Amt as Final.Amt,
SR.temp1.Num as Final.Num,
$(vRel) as Final.Relation
Resident SR Where SR.temp1.Num = $(vSRNum);
EXIT for;
ENDIF
NEXT

[derekjones]

Ah yes I see now, no SR are linked.  K, but is it robust enough that find the SO with double the SR value, surely two SRs could have the same value? (not in your example data, but in reality) Then how would you choose which SR is for which double SO? At customer level is this unique or again it's possible for same amount to eventually happen?