Four Stars

Insert records into multiple target tables based on Multiple Row counts

Let’s say I have more than have record in source table and I have 3 destination table A,B,C.

I have to insert first 1 to 10 records in A then 11 to 20 in B and 21 to 30 in C.
Then again from 31 to 40 in A, 41 to 50 in B and 51 to 60 in C… So on up to last record.

How to Achieve this?

Thanks In Advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars TRF
Twelve Stars

Re: Insert records into multiple target tables based on Multiple Row counts

To complete my 1st response, here is how it should look like:

Capture.PNG

In this example, data comes from tFixedFlowInput and consists of 12 records.

The exercise is to dispatch rows on 3 output (like you) for every group of 3 lines (instead of 10).

Here is the tJavaRow code:

if ((Integer)globalMap.get("s2") == null || ((Integer)globalMap.get("s2")) > 2) {
	Numeric.resetSequence("s2", 0);
	if ((Integer)globalMap.get("s1") == null || ((Integer)globalMap.get("s1")) > 2) {
		Numeric.resetSequence("s1", 0);
	}
	globalMap.put("s1", Numeric.sequence("s1", 1, 1));
}
globalMap.put("s2", Numeric.sequence("s2", 1, 1));

output_row.id = input_row.id;
output_row.amount = input_row.amount;
output_row.s1 = (Integer)globalMap.get("s1");
output_row.s2 = (Integer)globalMap.get("s2");

Fileds s1 and s2 are here only for the demonstration and can be removed.

Here is the tMap with the expression filter for each output:

Capture.PNG

And the result with 3 distinct groups as expected:

Capture.PNG

Hope this helps (don't forget to mark the topic as solved if so - Kudo also accepted).


TRF
3 REPLIES
Twelve Stars TRF
Twelve Stars

Re: Insert records into multiple target tables based on Multiple Row counts

3 output in a tMap with the right expression filter for each should be a solution.

The expession may be based on a sequence evaluation (see Numeric.sequence).

There many possibilities to implemente this king of algorithm, for example, have a 1st sequence varying from 1 to 10, each time it arrives to 10, restart to 1 and increment a 2nd sequence (varying from 1 to 3) which is used to decide which output the current record must go to.

The algorithm can be built using a tJavaRow (so you have tFileInputDelimited -> tJavaRow -> tMap -> tFileOutputDelimited 1, 2 or 3.


TRF
Twelve Stars TRF
Twelve Stars

Re: Insert records into multiple target tables based on Multiple Row counts

To complete my 1st response, here is how it should look like:

Capture.PNG

In this example, data comes from tFixedFlowInput and consists of 12 records.

The exercise is to dispatch rows on 3 output (like you) for every group of 3 lines (instead of 10).

Here is the tJavaRow code:

if ((Integer)globalMap.get("s2") == null || ((Integer)globalMap.get("s2")) > 2) {
	Numeric.resetSequence("s2", 0);
	if ((Integer)globalMap.get("s1") == null || ((Integer)globalMap.get("s1")) > 2) {
		Numeric.resetSequence("s1", 0);
	}
	globalMap.put("s1", Numeric.sequence("s1", 1, 1));
}
globalMap.put("s2", Numeric.sequence("s2", 1, 1));

output_row.id = input_row.id;
output_row.amount = input_row.amount;
output_row.s1 = (Integer)globalMap.get("s1");
output_row.s2 = (Integer)globalMap.get("s2");

Fileds s1 and s2 are here only for the demonstration and can be removed.

Here is the tMap with the expression filter for each output:

Capture.PNG

And the result with 3 distinct groups as expected:

Capture.PNG

Hope this helps (don't forget to mark the topic as solved if so - Kudo also accepted).


TRF
Twelve Stars TRF
Twelve Stars

Re: Insert records into multiple target tables based on Multiple Row counts

Let me know if it helped you, and mark the case as solved if the answer satisfied you (Kudo also accepted).


TRF