Hi,
Help require for below logic.
Table A and Table B
COL 1 in A mapped to COL 1 in B using condition if COL 2 in B = COL 4 in A.
For a specific value of COL 4 in A, there is only one record in B. But there might be many records with same value COL 4 in A. So everytime A needs a value, it will be COL 1 increment by 1 from B.
Finally, the last value has to be updated in COL 1 of B.
Please let me know if need any clarification.
Regards,
Kar
nik_reb (BOB member since 2013-04-04)
Jup. What have you done already yourself about this?
Johannes Vink 
(BOB member since 2012-03-20)
I used sql for A and datastore for B.
It is getting the value. But same value for all the records in A.
I’m very new to BODS. In fact this is my second job created.
nik_reb (BOB member since 2013-04-04)
Okay, I tried several answers but had to delete all. I am stating given of what you explained there is a contradiction.
Can you please come up with an example including data of what you want to achieve?
Start:
TableA has the values
1;1;1;1
1;2;1;1
1;2;1;1
and TableB has…
At the end of this load TableB should look like…
Werner Daehn 
(BOB member since 2004-12-17)
TableA has the values
1;1;1;5
1;2;1;5
1;2;1;5
1;2;1;5
1;1;1;3
1;2;1;3
1;2;1;3
1;1;1;3
1;2;1;3
Table B has the values
100;3
800;5
1200;6
At the end of this load TableB should look like…
105;3
804;5
1200;6
With type 3 there are five instances A has retrieved data from B. Hence final value is 105 in B.
With type 5 there are four instances A has retrieved data from B. Hence final value is 804 in B.
nik_reb (BOB member since 2013-04-04)
Take the count of values from Table A in a column and the actual value (3,5 etc) in another column and create a lookup.
Use this lookup to map with the actual value (ex 3 or 5) of Table B and populate the count value from the lookup in a separate column. In the next query transform add this count in the separate column with 100, 800 etc.
Doesn’t that work? Probably experts could give a better suggestion?
Arun
Arun.K 
(BOB member since 2011-10-18)
And the target will be
801;1;1;1;5
802;1;2;1;5
803;1;2;1;5
804;1;2;1;5
101;1;1;1;3
102;1;2;1;3
103;1;2;1;3
104;1;1;1;3
105;1;2;1;3
nik_reb (BOB member since 2013-04-04)
Did you do like what i explained below?
The look up should have only 2 columns COUNT, COL_VAL
For example, lets take the case of 5 and 3, so look up table would be
COUNT…COL_VAL
4… 5
5… 3
Lookup Table B against the lookup table created and add a new column COUNT_COL1 in table B. So the output of TABLE B would be -
100…3…5 (Count_Col1)
800…5…4 (Count_Col1)
In the next query transform add COUNT_COL1 with 100 and 800 to get the target.
Arun
Arun.K (BOB member since 2011-10-18)
Thanks Arun.
But the target needs to be having same number of columns as A with extra column whcih is a unique value from B with increment of 1 for every new column in A.
At the end B should remain same number of records as it was except the value should be updated with last used value (for a kind of records which in this case are 3,5 etc).
nik_reb (BOB member since 2013-04-04)
Table B is the Master Table in this scenario…
So lets take the first row
100;3
Table A – Query – column and Substr(column_name,-1) – Query 2 – Gen_rownum_groupby and column and Substr(column_name,-1)
. This gives you count,complete column and substr value
Now Question is whether value from table B will always be like 100;3 or can it be 103:3?
If Only 100;3 , Join A and B and add the count Value to First (100)…
Instead of Substr, if it is a single string ("100:3), you can also use word_ext.
I do not have access to BODS tool now as have switched Job (I still love the tool). 
Try and let me know if that Helps
Tarunvig (BOB member since 2011-01-18)
Okay, I think I got it. But let’s break the problem into two parts since you want to have two tables updated at the end, the target table and the table B.
Table Target:
Like the others suggested, I would read table A and lookup the starting value in table B and use gen_row_num_per_group() function.
TableA has the values
1;1;1;5
1;2;1;5
1;2;1;5
1;2;1;5
1;1;1;3
1;2;1;3
1;2;1;3
1;1;1;3
1;2;1;3
Table B has the values
100;3
800;5
1200;6
We build a dataflow with
TableA --> Query(lookup) —>
where we use the lookup_ext() function as new-function-call in the output, condition is tableA.col4 = tableB.col2, set to pre_load_cache(!)
Result of that query will be
1;1;1;5;800;5
1;2;1;5;800;5
1;2;1;5;800;5
1;2;1;5;800;5
1;1;1;3;100;3
1;2;1;3;100;3
1;2;1;3;100;3
1;1;1;3;100;3
1;2;1;3;100;3
This query should also sort the data based on col4 for the next query.
In a subsequent query we output all columns from this query and add one additional column called counter which is mapped to gen_row_num_per_group(col4). This column will contain a strictly growing number and restart at zero whenever col4 has a new value. Hence the order by in the previous query.
Result will be
1;1;1;5;800;5;0
1;2;1;5;800;5;1
1;2;1;5;800;5;2
1;2;1;5;800;5;3
1;1;1;3;100;3;0
1;2;1;3;100;3;1
1;2;1;3;100;3;2
1;1;1;3;100;3;3
1;2;1;3;100;3;4
As you see, all that is left is adding the 800 with the counter plus 1
1;1;1;5;800;5;0;801
1;2;1;5;800;5;1;802
1;2;1;5;800;5;2;803
1;2;1;5;800;5;3;804
1;1;1;3;100;3;0;101
1;2;1;3;100;3;1;102
1;2;1;3;100;3;2;103
1;1;1;3;100;3;3;104
1;2;1;3;100;3;4;105
Voila, we got your target table (after removing the extra columns in yet another query.
Above logic needs some fine tuning, e.g. what if there is no starting number for 3 or 5, then the starting number should be zero and not null.
Your table B is simple now, you split the data from above query into a parallel query where you select col4, max(col8) and group by col4.
Result will be
5;804
3;105
which you load into table B, loader set to autocorrect load so rows get updated. Table B has to have a primary key on the column containing the values 3 and 5 and 6, col2 in your terminology.
Werner Daehn (BOB member since 2004-12-17)
Thanks Werner.
I’m working on it now.
nik_reb (BOB member since 2013-04-04)