sudip

• Apr 23rd, 2015

 

Seq stg------>Sort stg--------> Trnsfrm stg-------->Tgt
1. read data in seq file stage.
2. in sort stage enable key column change as true.
3. in Trnsfrm define stage var, logic -> if key change =1 thn dslinkcity else stage var:,:dslinkcity
4. now define 3 col in output tab in trnfrm and use field function, lyk field(stage var,,,1) for city1 thn field(stage var,,,2) for city2 and field(stage var,,,3) for city3
hope this will work.

Nish

• May 25th, 2015

 

Hi there are 2 solutions to this scenario. It can be easily accomplished using the Pivot enterprise stage from DS 8.5 onwards by enabling the vertical pivoting aspect. Alternatively from DS 8.5 onwards you can also use Looping in Transformer.

When it comes to DS 8.0.1 these features are unavailable, so here you have to couple remove duplicates with a transformer.

@Sudip your answer is partly correct, yes the logic produces result but we are interested only in the last row of each name as it completely fills all 3 cities so staging the transformer into a remove duplicate stage enable retain last and output. this will result in inverse pivoting in earlier versions.

Sudip Barman

• May 27th, 2015

 

Hi Nish,

Yes you are correct. I missed that RM Dup stage.
So the design will be:
Seq stg------>Sort stg--------> Trnsfrm stg-------->Remove Dup stage--------->Trnsfrm stg-------->Tgt

1. read data in seq file stage.
2. in sort stage enable key column change as true.
3. in Trnsfrm define stage var, logic -> if key change =1 thn dslinkcity else stage var:,:dslinkcity
4. In remove dup stage enable retain last option and mapped output to next stage.
5. now take another Trnsfrm define 3 col in output tab in trnfrm and use field function, lyk field(stage var,,,1) for city1 thn field(stage var,,,2) for city2 and field(stage var,,,3) for city3
hope this will Help

Sudip Barman

• May 27th, 2015

 

Hi Nish,
You are correct. I missed RM dup stage.
So the job design will be:
Seq stg------>Sort stg-------->Trnsfrm stg1----->Renove Dup----->Trnsfrm stg2-------->Tgt
1. read data in seq file stage.
2. in sort stage enable key column change as true.
3. in Trnsfrm define stage var, logic -> if key change =1 thn dslinkcity else stage var:,:dslinkcity
4. in remove dup stage enable retain the last value option.
5. now take another Trnsfrm stage define 3 col in output tab city1, city2 and city3 in trnfrm and use field function, lyk field(stage var,,,1) for city1 thn field(stage var,,,2) for city2 and field(stage var,,,3) for city3
also this can be done using pivot stage.
hope this will help.

santhosh

• Jun 1st, 2015

 

We can directly achieve this by using Pivot stage

DS

• Aug 19th, 2015

 

Hi,
I tried using pivot expertise vertial option but it is not working since all the funtions are related to arithmetic like average,min,max. could you please tell me which function to use

RAM

• Aug 28th, 2015

 

You can use the following steps.
SRC --> SORT (by name) provide Key change=1 -->Transformer--> Remove Duplicates(Key as Name) (need to set property as duplicate for last one).
Transformer stage: you can get two stage variables.
1.svName 2) svAllctys.
svName Derivation: If keychange column=1 then y else n
svAllctys Derivation: If svName=y then CTY else Allctys:,CTY
then you will get the data after transform stage like below.
johnson,newyork
johnson,newyork,mumbai
johnoson,newyork,mumbai,delhi
On Remove Duplicate stage: you ommit the 1 & 2 record and get the last record in target.
Name, CTY
johnoson, newyork,mumbai,delhi
Pls try it.

Manas Mishra

• Sep 1st, 2015

 

This is a very simple requirement that can be achieved easily by just one transformer stage.
SeqFile--->Tranformer---->target(file/db)
Logic:
Read your file by setting up properties needed.
Transformer:
In the staget property in Input tab select hast partition, make your input column Name as key for Partitioning and Sorting.
In the stage variable make three stage variable in following ways:
1) svCur => link.Name
2) svConcat => if svCur = svPrev Then svConcat:,:link.City Else link.City
3) svPrev => link.Name
Output:
In the contraint give LastRowInGroup(link.Name)
Make four output columns
Name = link.Name
City1 = Field(link.Name,,,1)
City2 = Field(link.Name,,,2)
City3 = Field(link.Name,,,3)

manish

• Oct 5th, 2015

 

In case in INPUT city is same like, Delhi and Delhi then as per your answer is
Johnson, Delhi, Delhi
so it is wrong, in case I want
Johnson, Delhi
then what I have to do?

Reddy

• Oct 20th, 2015

 

seq/orcle stg-->sort_stg--->Trns_stg--->RD--->Target
Steps
-------------------
step1: sort_stg:
key=name
sort key mode: if it is sorted order take ((previous sorted)Dont sort) or (Sort) it
Createkeychangecolumn: true
it will add extra column keychange() column
step2: Trns_stg:
take one stage variable in transformer stage
SV: if dslink.keychange=1 then dslink.city else sv:,:dslink.city
map columns
name ---------> name
SV-------------->city
Step3: RD
keycolumn=name
Duplicate to retain: last
U will get a desired output.

varun negi

• Dec 8th, 2015

 

* You can do a sort and RD on name and city combination.
* Pass the output to copy stage followed by aggregator stage to calculate number of rows and then join the aggregator output link with another link with copy stage.
* Lets say we got the count in CNT column from aggregator.
* In transformer stage, initialize svCurrname with initial value = "-1" and then declare a new variable svPrevname as : If svCurrname = "-1" Then name Else svCurrname.
* Have another variable with svCurrname = name.
* Run the loop in Transformer stage as: @iteration <= cnt.
* Initialize the loop variable: svCititesString with initial value as "-1". Then write the derivation as - If svPrevname = name Then (If svCitiesString = "-1" Then City Else svCitiesString : "," : City) Else City.
* In the output of transformer, define the name, and in map svCitiesString loop variable to city column.

Neha

• Jan 2nd, 2016

 

We can directly use pivot job just set the properties and you can get the desired output.

Ram

• Mar 7th, 2016

 

You can do use pivot enterprise and give the properties as pivot for cty and group by name.
It will work.
Input --> Pivot Enterprise (vertical pivot) --> trg.

Alisha

• Aug 8th, 2019

 

Can you please elaborate the Staging variable condition? how we define staging variable and how we are putting in conditon

Ruchi Gautami

• Dec 30th, 2019

 

File -->> Sort -->> Transformer -->> O/P File
Sort:
Enable key column change as true.
Transformer:
Define one stage variable SVar
if key_change =1 then city else SVar:,:city
Define 3 columns City1, City2 and City3 in output tab in Transformer and use field function
field(SVar, , ,1) =City1
field(SVar, , ,2) =City2
field(SVar, , ,3) =City3