Retaining a permanent value

[djdinjan]

Hi all,

I have a requirement where i would have to do the following

1) For every row i select from the database i would have different combinations of Fail & Pass in one field concatenated as given below.
for ex: Fail|Pass|Pass, Fail|Pass|Fail, Fail|Fail|Fail

2) I would have capture these values and load only one value to a field in the target table, which could be explained as below

Fail|Pass|Pass
Fail|Pass|Fail
Fail|Fail|Fail
--------------
Fail|Fail|Fail - the final result to be loaded to the target table has to be this.

I will basically have to retain any Fail value and if for the second value i have a pass it should not overwrite the Fail.

Hope it is clear.

Would someone please help me in how to achieve this.

Thanks,
dinesh

[ray.wurlod]

Treat them as Boolean values (1 or 0). You can then combine them with summing in an Aggregator stage then pass them through a final Transformer applying Not(Not(InLink.colname)) to each to produce a final value. If you want a literal "Pass" or "Fail" for each column apply an Oconv() function with "S;'Pass';'Fail';'Unknown'" as the second argument.

[djdinjan]

Hi Ray,

While capturing the values i had tried @INROWNUM = 1 OR Field(column,'|',1) = 'Fail' i used the same code and created three variables and i had got the values in 1s and 0s and not as fail or pass.
For example:

I get for Fail|Pass|Pass as 1|0|0

I had tried to use these and aggregrate them using an aggregator stage, where i had mentioned the datatype as binary/varbinary but the job has been failing saying

Code: Select all

While creating "DSColumnC" object of type ""
While parsing derivation expression for column "DSLink126.Var1"
Syntax error at line 1, column 5, token "Var1"
Invalid argument type

I had not understood when you said to consider them as boolean values.

How do we consider them as boolean or convert the values to Binary?

Thanks,
Dinesh

Code: Select all

[djdinjan]

Hi Ray,

The results using the Not function which i am getting are

0|0|0
0|1|1

where 0 is considered as fail and 1 considered as pass

If we sum them up the result is 0|1|1 which is correct. But i would require to get the result of 0|0|0 showing all the fail ones.

If i had tried using Not(Not(value)) then i get the required results of 1|1|1 and 1|0|0 but the aggregator stage does an integer sum resulting in 2|1|1

any suggestions?

Thanks,

[ray.wurlod]

My logic had assumed 1 for Pass and 0 for Fail. You sum them first (in an Aggregator), then rely on the rule that 0 is False and any non-zero value is True. With your logic you may need one or three Not() functions, depending on what you want to do with the result afterwards.

In the syntax error, is Var1 a stage variable or a column name? You may have tripped over a reserved word here; try svVar1 if it's a stage variable or change the column name to something else.

[djdinjan]

Thanks for your suggestions.

I have been able to achieve it by using the stage variables

If the source value is 'fail' or the variable value is 'fail' then the value of the variable would be always fail.

This worked.

Thanks,
Dinesh