DSXchange

Hi,
I have a scenario where incoming column value needs to be compared, ex
If Src.Col1=A then 01
If Src.Col1=B then 02
.
.
If Src.Col1=F then 07

How can I parameterize it in a situation when the values of Col1 are dynamic.

Thanks,

There are too many ways to do this to answer, at least given the information in the request. If your column length is always 1 and starts with "A" you could index with a simple SEQ(In.Col)-64 to get a number. If you column is of variable length then this method won't work, but if all columns are of equal length you could use the INDEX() function to locate a substring text and return a position (which you can divide by the length to get a numeric series of answers without holes)

Hi Andrw,

The column lenght is not always the same, its varchar(3), I dont get it when you say all columns, I am asking about just one column, which has multiple values and if there is a new value added in the source (like AAW) then I want to avoid changing the if statement in the job.

Why not just do something with a Lookup? Build a table with the subsitution values and use that. When values change or are added, no change to the job itself would be needed.

Hi Craig,

Sounds good, one of our senoir developers also had an idea of using an external stage to read the column in and use parameters for values.

Looking at the options that are available.

Check out lookup_int16_from_string() in the Modify stage for small tables, or contemplate using a Lookup stage.