Interested to Buy Any Domain ? << Click Here >> for more details...
There are 2 files, Master and User. We need to compare 2
files and prepare a output log file which lists out missing
Rolename for each UserName between Master and User file.
Please find the sample data-
MASTER.csv
----------
Org|Tmp_UsrID|ShortMark|Rolename
---|---------|----------|------------
AUS|0_ABC_PW |ABC PW |ABC Admin PW
AUS|0_ABC_PW |ABC PW |MT Deny all
GBR|0_EDT_SEC|CR Edit |Editor
GBR|0_EDT_SEC|CR Edit |SEC MT103
GBR|0_EDT_SEC|CR Edit |AB User
USER.csv
--------
Org|UserName|ShortMark|Rolename
---|--------|---------|------------
AUS|charls |ABC PW |ABC Admin PW
AUS|amudha |ABC PW |MT Deny all
GBR|sandya |CR Edit |Editor
GBR|sandya |CR Edit |SEC MT103
GBR|sandya |CR Edit |AB User
GBR|sarkar |CR Edit |Editor
GBR|sarkar |CR Edit |SEC MT103
Required Output file:
---------------------
Org|Tmp_UsrID|UserName|Rolename |Code
---|---------|--------|------------|--------
AUS|0_ABC_PW |charls |ABC Admin PW|MATCH
AUS|0_ABC_PW |charls |MT Deny all |MISSING
AUS|0_ABC_PW |amudha |ABC Admin PW|MISSING
AUS|0_ABC_PW |amudha |MT Deny all |MATCH
GBR|0_EDT_SEC|sandya |Editor |MATCH
GBR|0_EDT_SEC|sandya |SEC MT103 |MATCH
GBR|0_EDT_SEC|sandya |AB User |MATCH
GBR|0_EDT_SEC|sarkar |Editor |MATCH
GBR|0_EDT_SEC|sarkar |SEC MT103 |MATCH
GBR|0_EDT_SEC|sarkar |AB User |MISSING
Both the files are mapped through Organization, Shor_mark.
So, based on each Organization, Short_Mark, for each
UserName from User.csv, we need to find the Matching and
Missing Rolename. I am able to bring Matching records in
the output. But really I don't find any concept or logic to
achieve "MISSING" records which are present in Master and
not in User.csv for each UserName. Please help out guys.
Let me know if you need any more information.
Note:- In User.csv file, there are n number of
Organization, under which n number Shortmark comes which
has n number of UserName.
- 1 Answers
- 4059 Views
- I also Faced
- E-Mail Answers
Answer / zer0
Create the mapping as below:
LKPTRANS
Master_SQ /\
\ / \
/JNRTRANS ---> EXP1 ---> AGG ---> EXP2 ----> EXP3 ---> Targ
User_SQ
In Joiner, use the join as per requirement. For the
scenario given a normal join is enough. Take all the ports
from Master_SQ and User_SQ into an expression after joiner.
From Expression pass it on to an aggregator. In aggregator,
group by based on the ports Master.ShortMark,
Master.Rolename and Master.Username .
From aggregator take the following ports - Master.Org,
Master.Tmp_UsrID, Master.ShortMark, Master.Rolename and
User.UserName
Make a lookup on the User.csv file and from Expression take
Master.ShortMark, Master.Rolename and User.UserName as
input into the lookup trans. Join on the basis of the input
ports and output LKP_UserName from the lookup
transformation.
Also, from EXP2 take all the ports as input into EXP3. Make
a CODE column in EXP3 with the condition IIF(ISNULL
(LKP_UserName),'MISSING','MATCHING')
From EXP3 pass all the ports into target. You will have
your desired answer.
| Is This Answer Correct ? | 0 Yes | 0 No |
How/where can i install Informatica software with oracle or teradata as database
What are the challenges you have faced in your project?
what is song in infrmatica...?
source table A having 100000 records. and target B table having 100000 records. now iam updating B records using update query. i don't know which record is update in target. write a sql query get all updated records from target table B.
what is diff between IN and EXIT commands
what are testing in a mapping level please give brif eplanation
i have a table like empid ename year month sal 1 x 98 jan 500 1 x 98 feb 500 1 x 98 mar 500 1 x 99 jan 600 1 x 99 feb 600 2 y 98 jan 600 2 y 98 feb 600 2 y 98 mar 600 2 y 99 jan 700 2 y 99 jan 700 and so on i want to find out totsal for every emp on year wise plz help me
If the records doesnt satisfy the filter condition where will it go?
Which tool yoU use to create and manage sessions and batches and to monitor and stop the informaticaserver?
Hi Experts, I have a source table like this. Name Number Raj 2 Ram 1 Sam 2 John 1 In the target I need the ouptput like the below Raj Raj Ram Sam Sam John We dont know the number value . It will be changing as n.. Please help me regarding this. Thanks, Nataraj V
draw informatica architecture 8.6 ?
What is the Rank index port in Rank transformation?
Teradata 
