Thursday 6 March 2014

Scenario 9: How to assign an unique ID for each and every group of records.

Scenario 9: How to achieve target below?

(Source)
ID  | COUNTRY
101 | INDIA
102 | NEPAL
101 | AMERICA
103 | AFRICA
102 | JAPAN
103 | CHINA

(Target)
SID|ID|COUNTRY
1|101| INDIA
2|101| AMERICA
1|102| NEPAL
2|102| JAPAN
1|103| AFRICA
2|103| CHINA

Theory/Analysis
As I have said in the heading we have to assign a unique ID for each and every group of records. Ok, got it Once again we need to compare current records and previous record if matched then increased SID otherwise start SID with 1. So, we have to use once again Expression Transformation and Variable Port. I am in love with these two :-)

Practically

Step 1: As usual Drag and Drop your Source to Mapping Designer.

Step 2:
Sorter
Sort the data based upon ID

ID     key (Checked)

Note: If your Source is relational table you can sort it by overriding Sql.

Step 3:
Expression
Create Six Ports

New_SID (Variable Port)= 1
ID_COMPARE (Variable Port) = IIF(ID!=OLD_ID,ID,OLD_ID)
ID_CREATE (Variable Port) = IIF(ID=OLD_ID,OLD_SID+1,NEW_SID)
OLD_ID (Variable Port) = ID_COMPARE
OLD_SID (Variable Port) = ID_CREATE
SID (Output Port) = ID_CREATE

Note: Please follow order above of ports otherwise you won't be getting expected output.

Step 4: Connect to Target

BINGO!!!



Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

2 comments:

  1. Hi,

    I used another approch but not have the same result.


    I created 4 variables like this:

    v_Compteur_ID (variable port) = v_Old_Compteur+1
    v_Old_Compteur (variable port) = IIF(v_Old_ID!=v_Current_ID,0,v_CompteurID)
    v_Old_ID (variable port) = v_Current_ID
    v_Current_ID (variable port) = ID
    Out_SID (Output port) = v_Compteur_ID


    Source files
    ---
    ID | COUNTRY
    101 | INDIA
    102 | NEPAL
    101 | AMERICA
    103 | AFRICA
    102 | JAPAN
    103 | CHINA
    104 | GERMAN
    104 | USA
    103 | ENGLAND
    106 | FRANCE
    104 | NIGER
    105 | MALI
    105 | ITALY
    ---

    My target out file is :
    -----
    #SID|ID|COUNTRY
    1|101| INDIA
    2|101| AMERICA
    1|102| NEPAL
    2|102| JAPAN
    1|103| AFRICA
    2|103| CHINA
    1|103| ENGLAND
    2|104| GERMAN
    3|104| USA
    1|104| NIGER
    2|105| MALI
    3|105| ITALY
    1|106| FRANCE
    ----

    Thank you in advance for your return

    ReplyDelete