|| Symbol - Append Dynamically
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
|| Symbol - Append Dynamically
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!
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!
Karthik
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
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'.
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'.
Karthik
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.)
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.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
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 ?
![Smile :)](./images/smilies/icon_smile.gif)
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 ?
Karthik
I'm assuming he meant something like this, off the top of my head:
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.
Code: Select all
(If Header_Valid_Flag = 'Y' then '' else Header_Reject_Reason : '||') : (If Footer_Valid_Flag = 'Y' then '' else Footer_Reject_Reason : '||')
Edited to use the proper concatenation operator. D'oh.
Last edited by chulett on Sun Oct 25, 2015 7:26 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
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.
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.
Karthik
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm