To stop records after the value *

Cherukuri · Post by **Cherukuri** » Wed Oct 03, 2012 5:38 am

Hi,

I have a question as below:

Input records:

SeqNo ValRecd
1 a
2 b
3 *
4 z

Should get out as below:

SeqNo ValRecd
1 a
2 b

The intension is not to process the records after the values '*' for ValRecd

Could please help to resolve... Thanks

ShaneMuir · Post by **ShaneMuir** » Wed Oct 03, 2012 5:42 am

Set a stage variable to look for and hold that value, and then use it as a constraint on the output.

swapnilverma · Post by **swapnilverma** » Wed Oct 03, 2012 5:53 am

Please do provide all information ... like your source type ( Sequential or RDBMS )

Assuming you have a sequential data source

After reading the data , In transformer you can put a simple check

stagevar --> If ValRecd = "*" then 1 else 0

add a constrain stagevar = 0

this should do it.

Cherukuri · Post by **Cherukuri** » Wed Oct 03, 2012 6:26 am

Hi SwapnilVerma,

Seems like you did not understand the requirement.

My source is sequencial file .
The output should be only the records above the '*' value for ValRecd field.

only i want out put as :
SeqNo ValRecd
1 a
2 z

I mean the records above the * value

Thanks

ArndW · Post by **ArndW** » Wed Oct 03, 2012 6:34 am

Cheru,

the answer supplied (almost) solves the problem as you described it.

svFoundStar IF(svFoundStar=0) THEN IF (In.ValRecd = '*') THEN 1 ELSE 0 ELSE 1

Then the constraint svFoundStar = 0

Cherukuri · Post by **Cherukuri** » Wed Oct 03, 2012 7:00 am

Explaning the question again:

Input records:

SeqNo ValRecd
1 a
2 b
3 *
4 z

Should get out as below:

SeqNo ValRecd
1 a
2 b

i dont want record as :
SeqNo ValRecd
4 z

so imean the records after value '*' should not porcess ..

so i wnat the out as :

SeqNo ValRecd
1 a
2 b

Thanks .

chulett · Post by **chulett** » Wed Oct 03, 2012 7:16 am

You don't need to explain anything again, we all get the requirement. What's not happening is you are not understanding the solution which has been provided.

Perhaps this revolves around the concept of "processing" the records after the "*". If in your mind that means "read" there's no way to do that as all records must be read. What people are giving you is a way to stop outputting records to the target after the "*" has been read. So, re-read both posted answers and give that a shot, it will solve your problem.

PhilHibbs · Post by **PhilHibbs** » Wed Oct 03, 2012 7:43 am

Of course, partitioning affects this. Only the node that gets the '*' record will stop outputting.

You could force the Transformer to run in Sequential Mode in the Advanced tab. I have found that this causes problems with downstream Aggregator stags.

Or, you could set the partition to have a Node Map Constraint so that it runs on a single node. A colleague has found that this causes problems with downstream Shared Containers.

Or, you could partition your data on a column that has a fixed value so that it all goes to one node.

Or, you could set up an automatic row number column on the input, pass an aditional lookup key column containing a sinlge '*' with each row, pass the '*' row out to a reference link, do a Lookup on the key column, and then only write out rows whose row number is less than the row number found in the lookup.

chulett · Post by **chulett** » Wed Oct 03, 2012 7:46 am

Of course... but the problem seemed simpler than that. I guess we'll see.

PhilHibbs · Post by **PhilHibbs** » Wed Oct 03, 2012 7:55 am

I don't see how the simplicity of the problem makes partitioning issues go away. If partitioning is not addressed, then the proposed solution will not work.

A simpler method might be to pass the input through an external filter on the way in. I don't have access to DataStage at the moment so I can't check how that is done. Also it's on Windows - is MKS Toolkit part of a standard Windows DataStage installation? If so then something like this in the external filter would do it:

Code: Select all

perl -pe "last if /^\*/"

chulett · Post by **chulett** » Wed Oct 03, 2012 8:08 am

PhilHibbs wrote:I don't see how the simplicity of the problem makes partitioning issues go away. If partitioning is not addressed, then the proposed solution will not work.

Not at all what I meant, Phil. There's plenty of time to bring partitioning into the picture, all I meant is that I believe we have a fundamental issue that needs to be solved first to move beyond a simple regurgitation of the problem.

And I purposefully left off any filter-style solutions to ensure the base concept of "processing" the records was addressed first.

PhilHibbs · Post by **PhilHibbs** » Wed Oct 03, 2012 8:21 am

I don't get it. Why leave out a filter-style solution? I don't see any fundamental problem. If the OP is having difficulty understanding a Transformer based solution - perhaps because the solution presented will not work because of partitioning - then suggesting a solution that avoids reading the records into DataStage in the first place is a perfect way to go? Why try to force the poor chap to understand the flawed suggestion (without pointing out the flaw) when there's a better solution?

I should also point out that my filter-based solution will only work if the file is a text file with traditional LF or CR+LF line-end characters.

chulett · Post by **chulett** » Wed Oct 03, 2012 9:21 am

Sorry but I don't really see where I'm being all that obtuse. Right now we have no idea what the OP thinks is the problem since all they done is state that people don't understand what they are trying to do and simply repeated the issue. So I felt we should address the fundamental issue of reading sequential media since we've seen people who don't really understand that you can't simply stop reading a file at a certain point. And that (to me anyway) seemed to be the case, the sticking point.

Perhaps it's just me but I felt that if the simple concept of reading all the records and contraining out the ones you didn't want wasn't being understood properly that we should make sure that aspect of the solution (and fundamentally that is the solution here) was understood before moving on to the other issues that were bound to crop up after that. I wasn't planning on "leaving out" anything, just wanted to make sure we put down a proper foundation before we built anything on it.

Does that make more sense?

PhilHibbs · Post by **PhilHibbs** » Wed Oct 03, 2012 9:59 am

Do we "have no idea what the OP thinks is the problem"? I don't want to be so rude as to characterise that statement as "obtuse" but... well, I guess it is obtuse so I'm going to have to. Sorry. I'm sure I understand the requirement, I'm sure the OP does as well, and if he had a problem understanding a flawed solution then I don't see the advantage in explaining why that solution will work when it clearly won't work without some unnecessary partitioning shenanigans.

chulett · Post by **chulett** » Wed Oct 03, 2012 10:08 am

That's fine, we'll just agree to disagree and see if anyone other than us ever comes back to the thread to continue the conversation.