source has 2 fields like
COMPANY LOCATION
IBM HYD
TCS BAN
IBM CHE
HCL HYD
TCS CHE
IBM BAN
HCL BAN
HCL CHE
LIKE THIS.......
AND I WILL GET THE OUTPUT LIKE THIS....
Company loc count
TCS HYD 3
BAN
CHE
IBM HYD 3
BAN
CHE
HCL HYD 3
BAN
CHE
PLZ SEND ME ANSWER FOR THIS QUESTION..........
Answer Posted / saibal kumar giri
Follow below steps-----
1.Read the data with sequential file or connector stage.
2.Use copy stage to streamline the data into two links
a.One data goes to lookup stage.
b.Second stream goes to aggregator stage.
3.In aggregator stage do a count(LOCATION) group by COMPANY.
you will be having data O/P from AGG as below
COMPANY COUNT
TCS 2
HCL 3
IBM 3
Use this output data as the reference link of lookup
stage.Where you have original source as master data.
4.Do a Inner join by lookup stage.
after lookup you will have data like
COMPANY:HCL LOCATION:HYD COUNT:3
COMPANY:HCL LOCATION:CHE COUNT:3
COMPANY:HCL LOCATION:BAN COUNT:3
COMPANY:IBM LOCATION:CHE COUNT:3
COMPANY:IBM LOCATION:BAN COUNT:3
COMPANY:IBM LOCATION:HYD COUNT:3
COMPANY:TCS LOCATION:BAN COUNT:2
COMPANY:TCS LOCATION:CHE COUNT:2
5.Then use a transformer stage along with stage variables
as below
SV1->link1.COMPANY
SV3-> if SV1=SV2 then SET NULL() else link1.COMPANY
SV2->SV1
Similarly for count also
Cheers!!!!!
Is This Answer Correct ? | 0 Yes | 1 No |
Post New Answer View All Answers
Describe link sort?
In Informatica,for the table I can find coreesponding dependent mappings.Likewise can I find the dependent jobs with all the information by using the table name
What is the roundrobin collector?
what are .ctl(control files) files ? how the dataset stage have better performance by this files?
How many areas for files does datastage have?
im new to this tool im now at project plz tell me step by step process how to design plz help me i wnt to go with exp for job plz give me d proper design and explination
Can you explain link buffering?
How do you import and export the datastage jobs?
What is ds designer?
If you want to use the same piece of code in different jobs, how will you achieve it?
Have you have ever worked in unix environment and why it is useful in datastage?
how can we create rank using datastage?what is the meaning of rank?
What is the importance of the exception activity in datastage?
In a batch if a job fails in between and you want to restart the batch from that particular job and not from the scratch then what will you do?
How to read multiple files using a single datastage job if files have the same metadata?