|| Symbol - Append Dynamically

[karthi_gana]

All,

I have designed a job to capture the reject reason.

Say for example, if the file is rejected because of "Header - Batch No is not correct" & "Footer Date format is incorrect".

I have to store it something like below

Header - Batch No is not correct || Footer Date format is incorrect

I have 7 error messages like this. But All these 7 will not come every time.


If I have 3 messages, I have split those 3 with || symbol, If I have 5 the same thing has to be applied.

1 || 2 || 3

1 || 2 || 3 || 4 || 5


Inputs are welcome!

[ray.wurlod]

If they come in as separate columns in the same row, use Column Import stage.

If they come in in separate rows, use Pivot stage to bring them into the same row, then use Column Import stage.

If they come in as a delimited string, change the delimiter to "||". Use the Ereplace function documented on DSXchange.

[karthi_gana]

It is not part of input, I generate all the reject reasons from the input columns by using stage variables and column comparison and store it in seperate columns.

Header_Valid_Flag
Footer_Valid_Flag
Header_Count_Flag
Footer_Count_Flag
File_Valid_Flag
RecordCnt_Match_Flag

The columns will contain either 'Y' or reject reason.

I have to merge only where the columns don't have 'Y'. But we don't know how many of them are <> 'Y'.

[ray.wurlod]

Had you told us that up front I would not have needed to surmise, nor to waste time on solutions based on those surmises.

The solution you require is nothing more difficult that a series of If..Then..Else statements concatenated together. Each of the generated strings will be preceded by "|| ". The whole will be enclosed in a function that removes the first three characters. (Or you could use suffixes and remove the final three characters.)

[karthi_gana]

I'm sorry for providing lack of info.

I am not still clear on your solution. Can you clarify ?

[chulett]

So... membership expired? Ray spelled out what you needed to do. Have you tried what he posted and have specific questions?

[karthi_gana]

not yet expired

I do understand his solution. But it is at high level.

Series of IF statement in Stage variable ? Which function ?

6 stage Variables...

first with error message or 'Y'

If the first one is <> 'Y' then

StageVar1 : '||' : stagevar2

else

stagevar2

something like this I have to generate...right ?

[chulett]

I'm assuming he meant something like this, off the top of my head:

Code: Select all

(If Header_Valid_Flag = 'Y' then '' else Header_Reject_Reason : '||') : (If Footer_Valid_Flag = 'Y' then '' else Footer_Reject_Reason : '||')

etc. etc. And then wrap it in something to remove the extra pipes at the end, assuming at least there was one reject reason. You could also test for that if you wanted by concatenating all of the flags together and checking against 'YYYYYY' and only building the reject string if it doesn't match.

Edited to use the proper concatenation operator. D'oh.

[karthi_gana]

I have handled in this way...

Trim(header_valid_flagsv : '|' : trailer_valid_flagsv :'|':header_count_flagsv,'|','R')


I have created stage variables and assigned values if header_valid_flag <> 'Y' then header_valid_flag else ''

so...

when it comes to transformer, either the reject reason or ''will come as the input from the stage variable.

say for example,

||Invalid Header Count

or

Batch no is missing ||

After applying trim with 'R' got the expected result.

After seeing your approach, I thought this question in my mind.

which method will perform better for huge volume of records ?

TRIM or IF THEN ELSE

But for now, I have only one record.

[chulett]

Not sure I understand your question... you need both. Or do you mean using some kind of IF-THEN-ELSE structure instead of the TRIM at the end?

[karthi_gana]

do you mean using some kind of IF-THEN-ELSE structure instead of the TRIM at the end?

yes

[chulett]

Stick with the TRIM.