How to process a condition on 124 occurences of a field?

[Jayakannan]

Hi,

My column defintion is as below.

1. Status_1 char(2)
2. Date_1 char(8 )
3. Status_2 char(2)
4. Date_2 char(8 )
.
.
.
247. Status_124 char(2)
248. Date_124 char(8 )

Condition to check: If any of these 124 status field's value is 'AB' or 'CD' or 'EF' then I need to get the corresponding Date field and set the the status as 'XYZ' in the output else Date field should be set to null and Status field should be set to 'ABC'.

For example
1) if the value of the field Status10 is 'CD' then I need map the field Date10 and set the status as 'XYZ' in the output.
2) Otherwise null and 'ABC' should be set in the output.

Any suggestions on how to achieve this?

[ArndW]

I ws going to suggest redefine & pivot but this might be simpler and more efficient:

1. Redefine this as one field "Data" with length Char(1240)
2. get the MAX of INDEX(In.Data,'AB',1), INDEX(In.Data,'CD',1) and INDEX(In.Data,'EF',1) into stage variable "svFoundPosition"
3. If "svFoundPosition" is 0 then set output date to null and status to "ABC".
4. Otherwise set the output date to In.Data[svFoundPosition+2,8] and status to "XYZ".

[bhasds]

Hi Jayakannan,

The desired result for the above scenario can also be achieved through transformer and funnel stage.

In transformer-

1.from transformer we can take 124 links each for one satus and date.

the derivation of status and date for each output link can be-

Derivation Columna Name
If status_1 = 'AB' Or status_1 ='CD' Or status_1 ='EF' Then "XYZ" Else "ABC" status_1

If status_1 = 'AB' Or status_1 ='CD' Or status_1 ='EF' Then date_1 Else @NULL date_1

2. In funnel stage we can accumulate all thel the 124 links and and pass through it choosing the funnel type option as sequence.


However, Arndw's suggetion is more efficient and make the deisgn easier.

[Jayakannan]

Thanks ArndW and Bhasds for your inputs, I have used the logic provided by ArndW and it works fine.