Loop Implementation

[naren6876]

Hi All,

My requirement is as below......

First get a record from Table A and then get the record from Table B and do the runn_bal - rem_amt.

If runn_bal - rem_amt <> 0 then update the runn_bal with runn_bal - rem_amt an Table A , and get the second record from Table B and and do the math again...and again.until it reaches 0. When it reaches 0 stop the process of Table B and update the runn_bal=0 for the first record in Table A.

Then get the second record from Table A and get the third record from Table B and process.so on and so.......

Code: Select all

Note:
There is no relation between rcvbl_id and remtc id

Table A	
rcvbl_id ano	rcvbl_amt	runn_bal status
1          1	      254	254         0
2          1	      3089  3089        0
3          1	      2	  2           0

Table B	
remtc_id ano	rem_amt	status
1          1	    254	    0
2          1	    3089	   0
3          1	    3089	   0
4          1	    2	      0
5          1	    2	      0

the output should be

Table A	
rcvbl_id ano	rcvbl_amt	runn_bal status
1          1	      254	0         1
2          1	      3089  0         1
3          1	      2	  -3091     1

Please let me know how can i achieve this.

Thanks in advance.

[anbu]

Are you updating Table A with runn_bal - rem_amt always?

[naren6876]

Are you updating Table A with runn_bal - rem_amt always?

I should update it...Or we can hold it in a variable until it reaches 0 and then update it.

[ray.wurlod]

Looks like you need to source a Cartesian product of A and B.

How is Status determined?

[anbu]

Are you updating Table A with runn_bal - rem_amt always?

I should update it...Or we can hold it in a variable until it reaches 0 and then update it.

Can you give better example for what do you mean by loop?

[naren6876]

Looks like you need to source a Cartesian product of A and B.

How is Status determined? ...

Thanks Ray..


For Table A....as soon as it reaches 0.change the status to 1.....

For Table B.....as soon as you read the record change the status to 1..

[naren6876]

Are you updating Table A with runn_bal - rem_amt always?

I should update it...Or we can hold it in a variable until it reaches 0 and then update it.

Can you give better example for what do you mean by loop?

Hi anbu,

I don't know what to call it....so called it as loop.....

The reason being...first i need read the record from A.....and loop it thru B records until balance reaches to 0....so i called it as Loop..

Thanks in advance.

[ray.wurlod]

There's no looping within the Transformer stage until the next version.

[naren6876]

There's no looping within the Transformer stage until the next version. ...

Hi All,

Thanks for your inputs.

Is there any other way to implement it (if not in DS)?

Thanks in advance.

[ray.wurlod]

Yes, as I indicated earlier, a Cartesian product (cross join) in your extraction SQL. Stage variables to keep track of what's happening. And patience to await the result of processing a very large number of rows (every possible combination).

[Gopinath]

Hi All,

My requirement is as below......

First get a record from Table A and then get the record from Table B and do the runn_bal - rem_amt.

If runn_bal - rem_amt <> 0 then update the runn_bal with runn_bal - rem_amt an Table A , and get the second record from Table B and and do the math again...and again.until it reaches 0. When it reaches 0 stop the process of Table B and update the runn_bal=0 for the first record in Table A.

Then get the second record from Table A and get the third record from Table B and process.so on and so.......

Code: Select all

Note:
There is no relation between rcvbl_id and remtc id

Table A	
rcvbl_id ano	rcvbl_amt	runn_bal status
1          1	      254	254         0
2          1	      3089  3089        0
3          1	      2	  2           0

Table B	
remtc_id ano	rem_amt	status
1          1	    254	    0
2          1	    3089	   0
3          1	    3089	   0
4          1	    2	      0
5          1	    2	      0

the output should be

Table A	
rcvbl_id ano	rcvbl_amt	runn_bal status
1          1	      254	0         1
2          1	      3089  0         1
3          1	      2	  -3091     1

Please let me know how can i achieve this.

Thanks in advance.

Hi,


Please find my understanding below, correct me if iam wrong.


Table A
rcvbl_id ano rcvbl_amt runn_bal status
1 1 254 254 0
2 1 3089 3089 0
3 1 2 2 0

Table B
remtc_id ano rem_amt status
1 1 254 0
2 1 3089 0
3 1 3089 0
4 1 2 0
5 1 2 0


STEP 1
---------

1) Extract Table A
2) Create a surrogate key starts with 1 (SurroKeyA is going to be as similar as rcvl_id)
3) Load into a dataset, which holds 3 records like below

Output of Step1: Dataset1
---------------------------

rcvbl_id ano rcvbl_amt runn_bal status SurroKeyA
1 1 254 254 0 1
2 1 3089 3089 0 2
3 1 2 2 0 3


STEP 2
-------

1) Extract Table B
2) Create a surrogate key starts with 1 (SurroKeyB is going to be as similar as remtc_id)
3) Load into a dataset, which holds 5 records like below

Output of Step2: Dataset2
---------------------------

remtc_id ano rem_amt status SurroKeyB
1 1 254 0 1
2 1 3089 0 2
3 1 3089 0 3
4 1 2 0 4
5 1 2 0 5


STEP 3
------

1) Sort the records on SurrKey and Join DS1 and DS2 on surroKey.
2) Do the transformation (A-B) in TFM and update the column in Table A as 0, update the column in TableB as 1
3) Use the DS to load the table A and table B by dropping the unwanted columns.


Please reply back if aim something here.

Thanks

[ray.wurlod]

Apart from the fact that it's a server job, you mean?

[Kryt0n]

Hi All,
If runn_bal - rem_amt <> 0 then update the runn_bal with runn_bal - rem_amt an Table A , and get the second record from Table B and and do the math again...and again.until it reaches 0. When it reaches 0 stop the process of Table B and update the runn_bal=0 for the first record in Table A.

What happens if the difference goes below zero?
What about summing up of Table B's column and then work out how many rows from Table A can fit in to the sum? The remainder can be plugged in to the last row