1.Without using stage variable how can we delete the duplicates using Transformer?

2.If we will remove duplicates using Transformer then minimum how many stage variable required for this?

bhargav Konduru

• Nov 21st, 2024

1.goto tfm stage
2.goto stage properties > goto partitions tab > select hash >select all the columns which duplicates>select sort > select unique
3. that will solve your problem

Mokshada

• Jul 10th, 2021

The prerequisite for this is data should be partitioned by a key and sorted.
Later, follow the below steps:
S2 = if input.column = S1 then 0 else 1
S1 = input.column
In constraint : S2=1

And, you will get the expected result.

Answer Question  Select Best Answer

Can any one help....
Source is.....
a
b
c
d

want target as.....
a,b,c,d

thanks in advance

Rajendra

• Jul 30th, 2024

Without "Pivot stage"
Source --> Transfer --> Target
In Transfermer Stage (Sequential mode)
1) Stage Variable: sv = sv:,:inputcolumn
2) Constraint: LastRow()
3) Derivation: Trim(sv,,,"L")

kalyanreddy

• Jan 30th, 2024

Using with pivot stage, we can convert the rows into columns ; source --> pivot -->target.

Answer Question  Select Best Answer

My input has a unique column-id with the values 10,20,30.....how can i get first record in one o/p file,last record in another o/p file and rest of the records in 3rd o/p file?

Bhargav konduru

• Nov 21st, 2024

In TFM stage do the below constraints 1). Link--> @inrownum=1 2).link --> lastrow() 3). link --> click the otherwise condition Some more things needs to do with above 1.In TFM properties set pa...

Prashant

• Nov 25th, 2018

If your output file is sequential file then you can you use filter property .

Answer Question  Select Best Answer

What is the order of execution in the transformer?

Ravi

• Dec 20th, 2024

1. Stage variable
2. it will check for any looping condition is there next
3. it will go for Constraints and derivations

amarnath

• Oct 15th, 2012

What is

1.Stage variables
2.Constraints
3.Derivations

Answer Question  Select Best Answer

How to load the different data files in single target table using one data stage job? Files are having different name and data but target table in static.

Zeeshan

• Dec 11th, 2024

Merge the different input files into funnel stage and load it into target

Hemant

• Nov 17th, 2018

If the input file has same metadata and matching file name pattern, then 1. Set Sequential file Read method property to "File Pattern" 2. Provide File Pattern Else, if different file name, then sp...

Answer Question  Select Best Answer

In detailed-
I have 5 input files that are completely different formats (the number of columns and metadata are different). How can I read all files using a sequential file stage? When I read the data from each file, how can I confirm which data belongs to which file?

Sincere request, Please suggest for the correct answer. I appreciate any help you can provide.

I have one scenario below, how can we achieve.

City1,City2,Distance
==============
blr, pune,1000 km
pune, blr, 1000 km

As we have same data in 2 records but need to delete any one of the duplicate record.

kalyanreddy

• Jan 30th, 2024

Use the remove duplicate stage and use the hash partition, select the key column as 3 rd column (1000 km).
and select record retained = first , it will remove the duplicate record, you can also use sort stage for this.

Sirisha d

 Profile Answers by Sirisha d Questions by Sirisha d

• Aug 8th, 2017

Job Design : Sequential file(I/p) --> Transformer --> Sequential file (o/p) In the Transformer stage , define 2 stage varibles st1 and st2. Derivation for st1 => If city2:city1=st2 Then 1 Else 0 ...

Answer Question  Select Best Answer

source table
name
A
A
B
B
B
C
C
D

In source table data like this
but I want traget table like this
name count
A 1
A 2
B 1
B 2
B 3
C 1
C 2
D 1


pls any one one solve this........

sachin kumar

• Jun 11th, 2024

Seq----transformer---seq.
Go to the stage variable properties create two staging variables first is s1 and second is s2 .
s2 = if inputcolumn = s1 then s2 +1 else 1
s1= inputcolumn
create new column is count.
s2= count

Rams

• Jul 10th, 2021

Seqfile---Sort----Trans-seqfile
Sort Stage---KeyChange column true
Transfor- in advance tab set mode-Sequence
Create one stage Variable: Stagevar= if KeyChangecol=1 then 1 else Stagevar+1
Create on extracolumn in output give Stagevar=Newcolname

Answer Question  Select Best Answer

If the paralle or seq job do not have configuration file..will it run successfully or not. ( If the job throws any error if yes what type of error it will be)

Somnath

• May 23rd, 2024

Without Config File the job wont even compile

Deepthi

• Aug 14th, 2022

No.it doesnt run

Answer Question  Select Best Answer

In source we have 10 files with different metadata and different Source files we need to load the files to target with 10 different target but we should do in a single datastage job how can we do this

what is the difference between dataset stage and sequential file stage? even one more to be added here, What is the difference between dataset stage and fileset stage?

Sourabh Kulkarni

• Jan 22nd, 2023

Deepak!, how to load a sequential file stage having data more than 2 Gb?

ravikumar

• Jul 24th, 2017

1) Sequential file stage we can use UNIX commands and in dataset stage we cant use UNIX commands.
2) Sequential file stage filename save as .txt and dataset - .ds

Answer Question  Select Best Answer

Input:
almost
banana
very
begins
order
in


Output:
almost IN ORDER
banana NOT IN ORDER
very NOT IN ORDER
begins IN ORDER
order NOT IN ORDER
in IN ORDER

Source have single column data, How can I get Target like row 1 goes to target 1, row 2 goes to target 2.

src :-
column_id
1
2
3
4
.
.
.
1000 ,
Target :-
Target1(1)
Target2(2)
Target3(3),
Target4(4)
.
.
Target1000(1000)

How can you achieve this scenario?

Yash Ojha

• May 9th, 2020

We can use the option "generate multiple files" in sequential file stage and then assign the data column as Key. SO with every change in key it will generate a new target file.

Rv

• Dec 7th, 2019

There are thousand rows, who will write thousand different where conditions and then will configure 1000 different files, is there any other solution which dynamic?

Answer Question  Select Best Answer

I have a source file having data like:
10
10
10
20
20
20
30
30
40
40
50
60
70
i want three output from the above input file, these output would be:
1) having only unique records no duplicates should be there. Like:
10
20
30
40
50
60
70
2) having only duplicate records,...

Ruchi Gautami

• Dec 30th, 2019

File -->> Sort -->> Transformer -->> O/P File
Sort:
Define the key field and in Property set Key Change column as TRUE.
Transformer:
In Constraints put the below condition:
KeyChange=1 for Unique record O/P
KeyChange=0 for Duplicate O/P.

shaheed

• Nov 20th, 2019

Src->Sggr->Trans->Target(3)
1) Unique- send as it is with link sort
2) Duplicate only- Loop through Count of each row count (constraint Count . 1)
3)Unique Only- Constraints Count = 1

Answer Question  Select Best Answer

Hi,

Can you please solve this in datastage..

my input is
name,city
johnson,newyork
johnson,mumbai
johnson,delhi

want output as
name,city1,city2,city3
johnson,newyork,mumbai,delhi

pls explain with steps
thanking in advance...

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 Cit...

Alisha

• Aug 8th, 2019

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

Answer Question  Select Best Answer

How to find out second highest salary as group wise by using datastage? Can you explain scenarios like pre row salary, next row salary, rank and dense rank

Ranga

• Sep 6th, 2019

Pre and next row salary can be get using analytical functions lag() and lead() functions in SQL

Answer Question  Select Best Answer

how i can achieve below logic in datastage
A B C D
B C D
C D
D

it can be number and symbol also
1/2
/2
2

SaravanaKumar

• Jul 27th, 2019

Let assume the input file has one column and the value is "ABCD"
Seq file --> Transformer --> Seq File

In Transformer Create Two stage variable
Len(Inut_Field) -> stageVar
StageVar + 1 -> StageVar1

Under looping give the iteration as @ITERATION

Answer Question  Select Best Answer

I have 10 Million records in source and target 5 million records were populated then job was aborted again i go and compile and run the job when that time job will run from 5million 1 record to remain records how?

Hari

• Mar 9th, 2019

Use Upsert function in database connector stage. Or use lookup stage to perform lookup on the source and reference as your target data and load into your target stage

Answer Question  Select Best Answer

Details:
Col
-----
C1
C2
C3
C4
C5
C6
C7
C8
C9
C10

we want to generate output like:
Col1 Col2 Col3
C1 C2 C3
C4 C5 C6
C7 C8 C9
C10

Can anyone please help me on this scenario?

Hemant

• Mar 4th, 2019

Use below constraints in xfm to move the data into 3 different columns. Make the xfm partition to run sequential.
mod(@inrownum,3) = 1
mod(@inrownum,3) = 2
mod(@inrownum,3) = 0

This will move first record to first output file, second to second and third record to third reference link.

prashant

• Nov 25th, 2018

Partion by Range

Answer Question  Select Best Answer

I have a sequential file it is having some records and the same file is having header and footer now my question is how to count the records in a file which is not counts the header and footer records and then transform the records in to target and then again we fetch the header and footer to that file which records are matched with the header

Sasidhar Punna

• Oct 24th, 2018

Cat | sed 1d ; $d | wc -l

sed 1d --- for removing 1st row
sed $d ---- for removing last row

Answer Question  Select Best Answer