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..........
- 3 Answers
- 8462 Views
- Patni, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / solomon
2 ways to do it
1- input -> Transformer -> RDP-> output
Sort the input on location, company
do a comparison in Stage Variable with prev record and keep incrementing a counter
pass it via Remove duplicate stage to retain unique records
2- input-> copy-> agrregator-> lookup-> RDP -> output
do the same as above using agg and lookup stage
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / sreepathi
Hi
Use stage variables in Transformer stage i.e IF THEN ELSE
IF ..condition comparing companies and use COUNT() function
in transformer for which field u want count
| Is This Answer Correct ? | 2 Yes | 3 No |
Answer / 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 |
What are the types of views in datastage director?
if i have two tables table1 table2 1a 1a,b,c,d 1b 2a,b,c,d,e 1c 1d 2a 2b 2c 2d 2e how can i get data as same as in tables?chandu how can i implement scd typ1 and type2 in both server and in parallel?chandu field1 field2 field3 suresh , 10,324 , 355 , 1234 ram , 23,456 , 450 , 456 balu ,40,346,23 , 275, 5678 how to remove the ,inthe fields?
which r the connectors used in san?
how many rows sorted in sort stage by default in server jobs
What is the version control how can i apply this in DataStage can any one tell me the anser
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..........?
What are the partition techniques available in your last project?
Can you filter data in hashed file?
What are some prerequisites for datastage?
I have 3 jobs A,B and C , which are dependent each other. I want to run A & C jobs daily and B job run only on sunday. how can we do it?
on how many columns we can perform aggregation in the aggregator stage?
hi i am bhavani, in real time data stage who is the source provide? and how to recevied in developer? Pls send me answer
Teradata 
