topic Re: detect change in column value & reset counter in Talend Studio

detect change in column value & reset counter

_AnonymousUser — Sat, 16 Nov 2024 12:44:49 GMT

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

Re: detect change in column value & reset counter

Anonymous — Thu, 11 Aug 2011 09:14:32 GMT

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

Re: detect change in column value & reset counter

_AnonymousUser — Thu, 11 Aug 2011 23:36:52 GMT

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