Question { IBM, 11838 }

can a fact table contains textual information

Answer

NO, Junk dimension have textual information.

Question { 6984 }

my soure table is emp having columns sal,deptno in the
deptno 10,20,30deptno row are there expected out
put is min(sal) of 10th deptno,max(sal) of 20th
deptno,mean(sal) of 30th deptno using aggregation stage

Answer

Where=Deptno=10 min(sal)
----------------->Agg1 --------> O/p1
Where=Deptno=20 Max(sal)
Seq--->Filter----------------->Agg2 --------->O/p2
Where=Deptno=30 Mean(Sal)
----------------->Agg3 ---------->O/p3

Agg:
Agg type=Calculation

Plz correct me if am wrong...

Question { IBM, 10871 }

one file contains
col1
100
200
300
400
500
100
300
600
300
from this i want to retrive the only duplicate like this
tr1
100
100
300
300
300 how it's possible in datastage?can any one plz explain
clearley..........?

Answer

Job Design:

Agg--->Filter1---------->|
| | Unique
file-->cp-------------------->Join---->Filter2---->target1
|
|-->Duplicate
Target2

Agg: use aggregator and select Agg_type=count rows and then give the Count O/P column=Cnt (User defined).

Filter1: give the condition Where=Cnt=1

U will get unique values like 200,400,500,600

Use Join (Or) Lookup stage: select left outer join

Filter2:

Where=Column_name='' (Duplicate values like 100,100,300,300,300)
Where=Column_name<>'' (Unique Values like 200,400,500,600)


u will get the right output....what ever the duplicate records.

Plz correct me if am wrong.....

Question { IBM, 7758 }

i have data like
sam
ram
sam
raj
ram

I want two targets
trgt1
ram
sam

trgt2

raj
how can i do this in datastage?

Answer

Small correction Ramu, Instead of Transformer stage use Filter stage it will give performance gain.

Question { 6552 }

I am having the 2 source files A and B and I want to get the
output as, the data which is in file A and which doesn't in
file B to a target 1 and which is in file B and which doesn't
in file A to a target 2?

Answer

FullOuter
FileA------->Join--------->Filter-------->T1
| |
| |
| |
| |
FileB T2
Use Fullouter Join Then
Filter-------> Where =Right.link=''
Where =Left.link=''
Nothing but A-B, B-A scenario.

Plz correct me if am wrong.....

Question { TCS, 6240 }

Hi All, I have a file. i need to fetch the records between
first and last records by using transform stage.

EX:-

Source:
EMPNO EMPNAME
4567 shree
6999 Ram
3265 Venkat
2655 Abhi
3665 Vamsi
5852 Amit
3256 Sagar
3265 Vishnu

Target:

EMPNO EMPNAME
6999 Ram
3265 Venkat
2655 Abhi
3665 Vamsi
5852 Amit
3256 Sagar

I dont wan't to Shree and vishnu records.we can fetch
another way also but How can I write the function in
transform stage?

Answer

If data is fetching from SeqFile, we can do this way also

Use Filter Command

Sed '1d;$d' FileName
It will delete frist and last records.

U will get answer, Plz correct me if am wrong....

Question { 10124 }

souce file having the columns like
name company
krish IBM
pooja TCS
nandini WIPRO
krish IBM
pooja TCS
if first row will be repeat i want the result like this
name company count
krish IBM 1
pooja TCS 1
nandini WIPRO 1
krish IBM 2
pooja TCS 2

Answer

SeqFile---->Sort---->Trms---->Dataset

Sort_Stage: Key=Company (Or) Name

Trms
----------

SV=Company
SV1= If dslink.Company=SV Then SV+1 Else 1

Add (count) column in Transformer stage
Map SV1 stage variable to Count column (SV1=Count)

Will get desired output..

Plz correct me if am wrong....

Question { UHG, 15001 }

Converting Vertical PIVOTing without using PIVOT stage in DataStage.
Ex:
DEPT_NO EMPNAME
10 Subhash
10 Suresh
10 sravs
Output:
DEPT_NO EMP1 EMP2 EMP3
10 subhash suresh sravs
2) How to implement Horizontal PIVOTing without using PIVOT stage.

Answer

Use Transformer Stage:
Take stage variable,
sv1 = sv1 : ' ' : DSLink.EMPNAME
Derivation : Trim(sv1, ' ','L')
U will get output.

Question { UHG, 15001 }

Converting Vertical PIVOTing without using PIVOT stage in DataStage.
Ex:
DEPT_NO EMPNAME
10 Subhash
10 Suresh
10 sravs
Output:
DEPT_NO EMP1 EMP2 EMP3
10 subhash suresh sravs
2) How to implement Horizontal PIVOTing without using PIVOT stage.

Answer

Use Transformer Stage:
Take two stage variable
sv1= if sv2=DEPT_NO then sv1 : ' ' : EMPNAME
sv2= DEPT_NO
Derivation : Trim(sv1, ' ','L')
U will get output.

Question { CTS, 15063 }

INPUT file 'A' contains:
1
2
3
4
5
6
7
8
9
10

input file 'B' contains:
6
7
8
9
10
11
12
13
14
15

Output file 'X' contains:
1
2
3
4
5

Output file 'Y' contains:
6
7
8
9
10

Output file 'Z' contains:
11
12
13
14
15

How can we implement this in a single ds job?

Answer

Use Full outer join
Right ------------->X table
| |
| |
Left--> Join----->Filter---------->Y table
|
|
Z table

Left.colname=Right.colname

Right.colname='' (A-B)
Left.colname<>'' and Right.colname<>'' (A intersection B)
Left.colname<>'' (B-A)

Will get desired output

Please correct if am wrong....

Question { Wipro, 11168 }

My source having following data as below,
AB1
Aim2
Abnv5
1An8bx
and my question is i need the Datastage job the following
as in my target

000AB1
00Aim2
0Abnv5
1An8bx
Please help me to achive this.

Answer

right(str("0",3):source_data,3)