DSXchange

Posted: **Fri Jun 01, 2012 1:51 pm**

DWS wrote: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.

Posted: **Fri Jun 01, 2012 8:48 pm**

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,

Posted: **Sat Jun 02, 2012 7:34 am**

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?

Posted: **Sat Jun 02, 2012 8:37 pm**

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

Partition and Sort on Emp_ID
Sort only on Seq_Num

Posted: **Wed Jun 06, 2012 12:38 pm**

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

Posted: **Wed Jun 06, 2012 11:27 pm**

That is great. Thank you a lot.

Posted: **Thu Jun 28, 2012 5:18 am**

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.

Posted: **Thu Jun 28, 2012 6:49 am**

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

Posted: **Thu Jun 28, 2012 7:28 am**

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.

Posted: **Thu Jun 28, 2012 10:48 pm**

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,

Posted: **Fri Jun 29, 2012 5:48 am**

A Roman with a bad stammer trying to say 95?

Posted: **Fri Jun 29, 2012 3:15 pm**

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

Posted: **Fri Jun 29, 2012 3:22 pm**

Please don't encourage him.

Posted: **Fri Apr 05, 2013 12:22 pm**

Can you please post the exact steps please

DSXchange

Seeking a best solution in DataStage

Run a problem