Why lookup is heavy weight and join is light weight operator?

sireesh vasantha

• Aug 3rd, 2015

LOOKUP use RAM to perform operations where as JOIN use buffer to perform operations

Answer Question  Select Best Answer

Is it possible to find out the maximum salary by using remove duplicate stage? Can you any one explain?

Thanks in advance

deepakatsit

 Profile Answers by deepakatsit Questions by deepakatsit

• Sep 10th, 2015

Hello Balu,
You can achieve Maximum salary by using remove duplicate.

Input File --> sortstage( Ascending or descending) --> Removeduplicate ( In remove duplicate Retain last if sorting ascending or retain first if sorting in Descending order ) --> output file.

Hope this will help.

Answer Question  Select Best Answer

I have two columns of data
My input data
Field Name Value
Customer_ID 100
Customer_Name ABC
Customer_City Kolkata
Customer_ID 200
Customer_Name BCD
CUstomer_City Mumbai

Customer_ID 300
Customer_Name CDE
Customer_City Delhi

Output date should be
Customer_ID Customer_Name Customer_City
100 ...

Pavan Kumar

• Sep 18th, 2015

You can use pivot enterprise stage to achieve the desired output.
Source >> Pivot Enterprise >> Destination. In Pivot set array size as three to spit the data into three records. Note that you are doing vertical pivot here not horizontal.

Answer Question  Select Best Answer

How to extract last 3 days data using SCD?

venkat

• Mar 28th, 2016

Use SCD Type 2 then get the last 3 days data.

Answer Question  Select Best Answer

I would like to know if you could help me out. I have a a file with 2 columns (TABLENAME and CLOB) and in the TABLENAME there are "Providers". I would like to know how to set them at number 5000 and then increment them, per provider. So if I have 5 providers per file, it would be 5000, 5001, 5002, 5003, 5004. This is a daily file so we will get a file everyday. So Day 2, the 1st provider must be 5005...

nikhil

• Dec 11th, 2015

From your question it appears that you want to generate a surrogate key with 5000 as seed value. This can be done through surrogate key generator using state file property and increment by 1. Set generate from last highest value also

Answer Question  Select Best Answer

I have heard of both Convert and Ereplace but I am stumped. I need to take every double dash (--) from my files and convert them to a single hyphen (-). What would be the best way to do this?

nikhil

• Dec 11th, 2015

Based on your need ereplace or change function will be better choice. Convert will treat (--) as 2 different characters.

Answer Question  Select Best Answer

Identify Header, Detail and Trailer records in the incoming file and verifying whether trailer count is equal to total number of detail records.

My input is
Header Columnname
Detail row 1
Detail row 2
Detail row 3
Trailer 3

Here I want to see whether total number of detail records is equal to total number of trailer record.
If so proceed...

Ram

• Jun 30th, 2016

You need to do file validation part using shell or any other scripting as before subroutines.

Answer Question  Select Best Answer

Basically which data model do we use in the project?

Pavan Kumar

• Jul 5th, 2016

Hi alok It totally depends on the project. 1) Entity Relation model or ER model is used when the data source is small as the number of joins are less. 2) Dimention model a) Star : Used for...

Answer Question  Select Best Answer

How will you separate the records with special characters in a column. For example
If suppose the column contains
Name*
&Name
Na*me
$Name
Na$me

I need to separate these records alone.

Shrushti

• Jul 16th, 2016

Source - transformer - target.
In transformer,
Namesp --- If alphanum(Name) then 1 else 0
Constraint --- Namesp=0

Answer Question  Select Best Answer

Why we are using orchadmin to remove dataset. why we are not using rm commond in remove dataset..and why you need dataset remove waht is the reason?

Reddy

• Dec 19th, 2016

Dataset is not like a normal file & it is not a single file...based on number og node in config file, those many data files and one descriptor file..are created for each dataset. Hence removing using rm command will corrupt the dataset and will not be removed properly.

Answer Question  Select Best Answer

I have 10 files, in 5 files having same data(3 columns), 5 columns having same data(4 columns). How can I perform funnel and what are stages required from me?

Srinivas

• Oct 7th, 2016

Use column generator to add columns to make the column count is same and also the column names should be same in each files. Then you can perform funnel.

Answer Question  Select Best Answer

I have a source file having 4 columns. How can I store first 2 columns values into one target and the next 2 columns values into another target? Can anyone suggest me here how can I achieve this using Copy stage and without using Copy Stage?

Ram

• Aug 4th, 2016

Hi

you can try with 1) copy stage & 2) transformer stage

Thanks!

Answer Question  Select Best Answer

Can we change the configuration file during run time? If then how?

hema

• Aug 3rd, 2016

First we need to add the environment variable(apt_config_file) to the job parameters

Answer Question  Select Best Answer

I have to design a job, if I run the job it should get 10 records from emp, if another person runs he should get all the records form emp. How to design such a job?

Shaktimaan

• May 2nd, 2018

If you are using Sequential file stage, there we have an option "Read the first row", make it parametrized.
and pass the value to the job using dsjob command as the parameter. also, you may like to use Invocation ID as you need to run the job with the different instances.

Answer Question  Select Best Answer

What is the minimum number of input and output required for Lookup ?

vibgyss

• Aug 24th, 2016

Min of 2 inputs is required by lookup stage and 1 output link is mandatory for any type of lookup i.e. we have 4 types of lookups from 8.0 version those Normal lookup, sparse lookup (only supports 2 inputs one is primary and second is reference), range lookup, caseless lookup

Answer Question  Select Best Answer

How to handle rejects in Transformer & How many rejects a xfm support?

vibgyss

• Aug 24th, 2016

Transformer
The transformer only rejects the data of a column which has nulls and that column is used in the expression then that type data is rejected by transformer stage

No other data is rejected by Transformer Stage
Only one reject link supported by transformer

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

I have Customer file & Dealer file.

Dealer file have dealer list who all are present on that day. Count of records may vary based on available dealers.
Customer file have customer list. Count on customer records also may vary daily.

The scenario is Dealer has to call customers in daily bases. We should provide customers record to them. So that they can call the customers....

Chaitra

• Aug 16th, 2017

Use count of customer id - cnt_cst and dealer id - Cnt_dlr in aggregator stage
In transformer,
define a stage variable (sv1) as cnt_cst/cnt_dlr.
@iteration

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

In a Column 123abc456xyz convert into two files like 123456 fil1 and abcxyz file2

Sandeep

• Apr 8th, 2018

By using convert fun we can achieve it:

in Tx->functions->string-> select convert

Convert (0123456789,,inputLink) ==

Target1 :abcxyz

Convert (abced....xyz, ,InputLink)==
Target2 : 123456

Answer Question  Select Best Answer