Flag Logic

[Diya]

Guys,

Im facing the following problem in the Transformer logic.

I have two input columns to my transformer 'Value' and 'Type'
I have one output 'flag' column which is to be populated as 'Y' or 'N' as per the logic below.

"For each 'Type' I should assign the value 'Y' to the output flag for that record for which 'Value' is maximum."

Example:

Type, Value, Flag,
A, 122, N
A, 136, Y
A, 126, N
B, 111, N
B, 156, Y
C, 187, Y
C, 163, N
C, 145, N

Can anyone give me the logic to be used in the transformer?

Thanks in advance,
Diya

[ArndW]

Looks like a lengthy statement like IF In.Type="A" and In.Value=122 THEN 'N' ELSE IF In.Type = "A"... is your best best, since there is no discernable logic for the values of Type and Value for setting the flag.

[Diya]

I just gave u an example.There are 1000s of records in the input table with so many types.For each type i should pick up the row in which 'value' is maximum and then assign vale 'Y' to the flag...for all other rows i whould assign 'N'....

so if there are 'x' types I will have 'x' rows with flag='y' and the rest of the rows with flag='N'.

So writing a lengthy statement will not work...

Can u suggest any other idea...

Thanks
Diya

[kcbland]

Looks like you need to run the data to a hashed file with all rows flagged as "N" and also to an Aggregator getting the max for the group and then output from the aggregator to a transformer where you set the flag accordingly, then write to the same hashed file. Then, move the data out of the hashed file.

[ray.wurlod]

Sort by Type then in descending order by Value. Use stage variables to detect a change in Type and set the flag to 'Y' where this occurs and to 'N' otherwise.

Code: Select all

svIsChanged  @INROWNUM = 1 Or InLink.Type <> svPrevType 
svPrevType   InLink.Type
svFlag       If svIsChanged Then "Y" Else "N"

Note that svIsChanged must occur (be evaluated) before svPrevType. Make sure that stage variables are initialized.