Input Value Change

[Diya]

Can anyone tell me how to detect whether a particular input column in a transformer has changed to a new value or not?

[meena]

Hi Diya,
Can you explain us with little more details.

Can anyone tell me how to detect whether a particular input column in a transformer has changed to a new value or not?

Thank you

[Diya]

Hey I have an input column which comes in as a series of As Ps Xs etc ...for the first of each type I want to set the o/p clumn to 'Y' for all others o/p column shd be 'N'....I mean for the first A ishd assign 'Y' ....then for the first P....then for first 'X' like that...

[narasimha]

If you cannot check in your output, you could also push the same results into a sequential file.
From the transformer drag all the links going to your output into the sequential file input.
Also try to restrict the number of rows passing through, if you want to look into a smaller result set.

[meena]

Hi Diya,
If you are only extracting one column then try doing something this way. I am not sure abt your source. if it is a table you can extract unique rows through SQL and use this as an look up to the original table.Write constraints ( lookup=i/p then assign Y else N in the column) and insert the rows into table.If the source is a file , pass it to hash file and write constraints and merge the files.
I am not sure whether it works or not but just trying to give an idea.

thank you

[kumar_s]

Sort the records based on the required column. You need to compare the previous record with current record . This can be done easily via stage variable.

User three stage variable in transformer.

Code: Select all

vOldRec = ''
vIsEqual  = ''
vCurrRec = ''
Initialise all the variable to ''

In transormer derivation,

vOldRec = vCurrRec
vIsEqual = If vOldRec = vCurrRec  Then 'N' Else 'Y
vCurrRec = In.Col

vIsEqual is your indicator now.

[Diya]

Thanks meena and kumar...

Kumar the stage variable logic is working...thnx

[chulett]

Can anyone tell me how to detect whether a particular input column in a transformer has changed to a new value or not?

The simple answer to this is sure - with stage variables. However, the devil is in the details.

Is your input data sorted? Once you've found your 'first A' will all the rest of the A's come right after it or could they anywhere? Sorting may be problematic if you need to preserve which A is the first A but I suppose a stable sort would help there.

How about a different approach, one that doesn't care if your input data is sorted? Hashed file. One field, the key field, to hold this input column to check. In the transformer, link to this hashed file twice - one as a reference lookup and again to write to it. Do a lookup against the hashed file with your value. Lookup NOTFOUND = new value, so set output column to 'Y' and write that key value to the hashed file. Lookup Not(NOTFOUND) = already seen, so just set output column to 'N'.

Two things to note. Don't cache the reference hashed file. Find a way to clear the hashed file each run. The why's and how's of each have been discussed here many times.

And for others out there in the home audience - yes, this is yet-another-way to check for duplicates.

[chulett]

Ah... a little late I see, too long spent polishing the vowels. Still something to consider or to take note of in the future.