process current record based on value from previous record

[haisen]

Requirement:
1) For record of the same ID, check that if CODE only contains * then derive as '1' and if contains both * & # then derive as '2'.
2) There is no fixed number of occurances for the same ID.

Restriction: We do not have pivot installed.

Sample Data:
ID CODE
1 A
1 *
1 B
1 C
2 D
3 *
3 #

I have tried to use Stage Variables, but it seem like the value assigned is from the same row instead of the previous row.

TIA

[sud]

Stage variables is the way to go (do hash partition and sort by ID column first). Well, you have to have a flag variable and two other variables with current and prior values. You can use these definitions of stage variables in the same order:

current_id = link.ID

id_changed_flag = if (current_id <> prior_id) then 1 else 0
code_flag(initially 0) = if (id_changed_flag = 0 and link.CODE = '*' or '1') then code_flag + 1 else if id_changed_flag = 1 then 0 else code_flag

prior_id(initially link.ID) = current_id

Now, the output constraint of the transformer should probably be id_changed_flag = 1 and the value of code_flag is the derivation itself.


..... something along these lines ...

[prasanna2883]

Hi,

Could you please specify the Output format you require. The question is not so clear, it would be easy to understand if the output is posted.

[haisen]

Actually, I just got a very roundabout way to get what I want.
Sample Main Data:
ID CODE
1 A
1 *
1 B
1 C
2 D
3 *
3 #

Result:
ID FINDING
1 * Found
2 NIL
3 * and # found

Method:
1) Based on the file, get a distinct list of ID that contain * and another list for #.
2) Using the main data, do a lookup to the 2 list giving the colums: ID, Code_1, Code_2.
3) Do a if else on Code_1 & Code_2.

However, for another job, I will need to append all the code of the same ID. I have yet to try using stage variable to get the previous record for use in the next record.

Result
ID Code
1 A*BC
2 D
3 *#

Thanks