One Star

detect change in column value & reset counter

Hello,
I'm relatively new to Talend. I know how to open an input and write to an output. But, I'm not sure how to approach this current problem. I need to read a table with 2 columns and write to a table with 3 columns. The 3rd column's value, generated in the transfer, resets to zero whenever the value in the second column changes between rows. Otherwise, the 3rd column's value increments by one. I've provided an example below. Could anyone recommend how to proceed?
thank you,
-david

ORIGINAL SOURCE DATA
ID PARENT_ID
1 0
2 0
3 0
4 1
5 1

GENERATED TARGET DATA
ID PARENT_ID CHILD_NUM
1 0 0
2 0 1
3 0 2
4 1 0
5 1 1
2 REPLIES
One Star mpa
One Star

Re: detect change in column value & reset counter

Hi,
I would do this by using the Numeric.sequence function.
(Change the input and output depending on what input/output source you are using)
TmssqlInput --> tMap --> Tmssqloutput
In the input component you would have your 2 columns in the schema (ID, Parent_ID)
In the output column you would have the 3 columns in the schema. (ID, Parent_ID, Child_num)
In the tMap you just drag the first 2 columns from input to output. (ID, Parent_ID)
For the CHILD_NUM column you can add an expression. For example: Numeric.sequence(row1.ParentID,0,1)
Hope it was clear for you. Good luck.
Regards,
Mario De Pauw
One Star

Re: detect change in column value & reset counter

Thank you! I understand your suggestion, but am actually having trouble on another issue. I simplified the schema a bit in my post. There are actually a number of other columns but I am writing the same values for all rows. However, I'm getting this error:
Exception in component tSQLiteOutput_1
java.sql.SQLException: near "#000000": syntax error
at org.sqlite.DB.throwex(DB.java:252)
at org.sqlite.NestedDB.prepare(NestedDB.java:96)
at org.sqlite.DB.prepare(DB.java:62)
at org.sqlite.Stmt.execute(Stmt.java:62)
I've written to a CSV value to help troubleshoot. The "#000000" mentioned in the error is being written to a text column. I'm not sure why that would be a problem. I think some booleans right before and after may be the culprits. In the CSV file, booleans are written as text "false". But in my sqlite viewer, they show up as the number zero. Does Talend handle booleans in sqlite correctly? If that isn't the problem, I'm not sure how to debug it further. I have the right number of columns and they are all the same datatype as in the sqlite file.
Any suggestions?
-david