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
process current record based on value from previous record
Moderators: chulett, rschirm, roy
Re: process current record based on value from previous reco
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 ...
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 ...
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
-
- Participant
- Posts: 26
- Joined: Tue Oct 23, 2007 4:07 am
- Location: Blore,India
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
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