Seeking a best solution in DataStage

[chulett]

If anyone in future wants to know the exactlly steps, I could post those in the forum.

It's always appreciated when people post their solutions.

[Billyqing]

Hi

I have a similar situation and I woulk like to use loop in Transformer Stage.
Could you send me the details of step of your job?

Thanks,

[vamsi.4a6]

To achieve the o/p we need to partition on

a) EmpID alone or
b) EmpID and Name
and also records should be sorted based on Seq_Num with asc mode in link sort with in each partition.Please correct me if i am wrong?

[mobashshar]

Since Name will relate to specific Emp_ID, I will suggest as follows:

Partition and Sort on Emp_ID
Sort only on Seq_Num

[DWS]

Hi

You need to create 5 stage variables as:

(1). NumSavedRows
SaveInputRecord()

(2). IsBreak
LastRowInGroup(Input_Lnk.ETL_KEY)

(3). LastRowConcat
IF IsBreak Then '' ELSE LastRowConcat : Input_Lnk.DESCRIPTION

(4). ConcatDesc
IF IsBreak Then ConcatDesc : Input_Lnk.DESCRIPTION Else LastRowConcat

(5). NumRows
IF IsBreak THEN NumSavedRows ELSE 0

You also need to create a loop variable as:

SavedRowIndex
GetSavedInputRecord()

The output link as

EmpID ---Inutput_Lnk.EmpID
Name---Inutput_Lnk.Name
Description----ConcatDesc

The constraint will be

LastRowInGroup(Input_Lnk.ETL_KEY)

The loop condition is

@ITERATION <= NumRows

[Billyqing]

That is great. Thank you a lot.

[DWS]

Hi Everyone,

I have to bring it back since I have run a problem.
I would like to share this with all of you and also want to get a solution for the problem.

The problem is:

If the key has more than 1 rows in the source, then result of the concatenation of multiple rows is good. But it is not good when the key only has one row in the source.

Does anyone has this experice and know how to solve this isssue?

I also wanted to know what the reason is?

Thank you very much in advance.

[chulett]

You'd have to start by explaining to us what "not good" means. Examples would be nice, too.

[DWS]

Here are the details:
Please note for output row 102, it should be Re-hied for Decs column, but it has appeared some additional text which it should not have.

Input:
--------------------------------------------
Emp_ID Name Seq_Num Desc(Varchar(10)
------ -------- ------- -----------------
100 Tiger W 1 I am
100 Tiger W 2 an
100 Tiger W 3 ETL
100 Tiger W 4 developer
100 Tiger W 5 working fr
100 Tiger W 6 om
100 Tiger W 7 home
100 Tiger W 8 everyday
100 Tiger W 9 from 10 am
100 Tiger W 10 to 10 pm.
101 Kevin Q. 1 Start from
101 Kevin Q. 2 June 2012.
102 John Smith 1 Re-hired
------------------------------------------------

Output
------------------------------------------------
Emp_ID Name Desc(Varchar(1000)
------ ---------- --------------------------------------------------------------------
100 Tiger W I am an ETL developer working from home everyday from 10 am to 10 pm.
101 Kevin Q. Start from June 2012.
102 John Smith XXXXXXXXXCCCCCVVVVVVV Re-hired.

[jwiles]

Is "XXXXXXXXXCCCCCVVVVVVV" the actual output or did you just type that yourself? I don't see X, C or V in your data here........just wondering

Do you initialize the variables at the job start (Initial Value in the stage variable properties) and when your key changes? Something along the lines of:

Code: Select all

if NewKey then input.Desc else svDesc : input.Desc

(your code may vary...may be similar to DWS' examples)

Regards,

[ray.wurlod]

A Roman with a bad stammer trying to say 95?

[FranklinE]

The snoring sound made by a hibernating bear with a lisp?

[chulett]

Please don't encourage him.

[sendmkpk]

Can you please post the exact steps please