Monday 17 March 2014

Scenario 13 A: How to Convert Row to Column

Scenario 13 A: How to Convert Row to Column

How to achieve target below...

(Source)

SUBJECT,MARKS
JAVA,60
ORACLE,50
INFORMATICA,78
JAVA,45
ORACLE,55
INFORMATICA,52

(Target)
Column1  Column2   Column3
JAVA ORACLE    INFORMATICA
60             50             78
JAVA ORACLE    INFORMATICA
45              55             52

Do, you wan't to write it into single column? No issue I have covered both, for single column you have to skip step 5.

Theory/Analysis
After analyzing source and target we can conclude that first we have to convert row to column then column to row. Confused.... What are you talking about first first row to column then column to row....
Wait wait... Let me help you.
First Row to Column: This we have to achieve because JAVA, ORACLE, INFORMATICA. Is in't it converted from row to column. Agreed? OK..
Second Column to Row: This we have to do because in Source we have two columns and second column data is coming to second row.
Hope it's clear by now. Let's do it Practically

Practically

Step 1:
Drag and Drop your source and target to Mapping Designer.

Step 2:
Expression
Create 10 Ports

ID (Variable Port) = 1

NEW_ID (Variable Port) = IIF(OLD_ID=3, ID,  OLD_ID+1)

OLD_ID (Variable Port) = NEW_ID

SUBJECT_CONCAT (Variable Port) = IIF(NEW_ID = 1 OR ISNULL(OLD_SUBJECT), SUBJECT, OLD_SUBJECT||','||SUBJECT)

OLD_SUBJECT (Variable Port) = SUBJECT_CONCAT

MARKS_CONCAT (Variable Port) = IIF(NEW_ID =1 OR ISNULL(OLD_MARKS), MARKS,OLD_MARKS||','||MARKS)

OLD_MARKS (Variable Port) = MARKS_CONCAT

O_NEW_ID (Output Port) = NEW_ID

O_SUBJECT (Output Port) = SUBJECT_CONCAT

O_MARKS (Output Port) = MARKS_CONCAT

Step 3
Filter
Filter Condition = O_NEW_ID=3

Step 4
Normalizer
Create one Column in Normalizer Tab

Column Name  Occurs    Data Type   Prec
COLUMN1        2                String          100

Step 5
(Unnecessary Step, Your choice, If you want to skip this because you want to load data in single column target go ahead connect COLUMN1 port from Normalizer to target)
I am using this step for better understanding.. Split column value in three different column based upon comma.
Expression
Create three ports
COL1 (Output Port) =SUBSTR(COLUMN1,1,INSTR(COLUMN1, ',',1, 1)-1)

COL2 (Output Port) =SUBSTR(COLUMN1, (INSTR(COLUMN1, ',',1, 1)+1),  ((INSTR(COLUMN1, ',',1, 2)- INSTR(COLUMN1, ',',1, 1)-1)))

COL3 (Output Port) =SUBSTR(COLUMN1, (INSTR(COLUMN1, ',',1, 2)+1))

Step 6: 
Excell Target: At session level for target file properties have extension in .CSV and enjoy your excell target. What you said? It's not excell target. Ok!! Give extension .xls but here you will receive an error while opening your target file. Ignore that and proceed to see the data and. If you are giving .xlsx then you won't be able to open it at all. It will throw error file has been corrupted or not in proper format.
Conclusion: Informatica doesn't create excell target. To be safer side what we are doing in real time create target in .csv format and have script to convert it to excell file with .xls or .xlsx extension.

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

4 comments:

  1. can any one tell the diff b/w star and snow flake schema in real time

    ReplyDelete
  2. First of all could you please tell us... What are the difference you already know for general (not in real time) ?

    ReplyDelete
  3. Star Schema dimensions not normalized. Snow flake dimensions normalized tables.
    Star Schema doesnt have sub dimension tables. But Snow flake have sub dimension tables.
    Star Schema less joins, So performance fast. Snowflake have more joins, So performance less.

    ReplyDelete
  4. Hi, Virus Man

    What do you mean by not Normalized? Are you referring de-normalized. I think Yes... No personal feelings... but these are some points through which we are checking whether candidate is genuine or does he really have good experience...
    In nut shell what words and sentence you are using that impact a lot..

    Could you please tell me what is the difference between your First point and Second point? Isn't it same?

    Frankly, My final goal was to drag you at a point to understand there is no difference in Real time and What you are learning concept wise.. Both are same.
    Let's take a Case Study:
    Suppose I am a tailor and I know basic how to stitch shirts and paints. Now If I am joining a big Garment manufacturer company as a tailor. So, is there any difference would be there in stitching Paint and Shirts... Definitely it would be same... The difference would be in design and patterns but basic would be same.

    Conclusion: There is no basic difference between Real time and Learning. If you have basic just you have expand it at large scale. That's it. So, work on Foundation first... then think about wall and roof.

    ReplyDelete