get me the resultant
input:- 1 x,y,z output:- 1 x
2 a,b 1 y
3 c 1 z
2 a
2 b
3 c
Answer Posted / zer0
I think this might work for you .........
Use the following flow :
Source ---> SQ ---> Expression ---> Normalizer ---> Filter -
--> Target
In the expression use variable ports to form 3 columns
depending on the values received in Column2. I mean to say
the given value is X,Y,Z in column2 so create 3 ports and
eac port will have 1-1 values i.e. X then Y then Z.
For this use SUBSTR and INSTR functions.
SUBSTR to get the part of the string and INSTR to find the
position.
VARIABLE_PORT1 ---> substr(column2,1,1)
VARIABLE_PORT2 ---> IIF(instr(column2,',',1,1)!=0,substr
(column2,instr(column2,',',1,1)+1,1),NULL)
VARIABLE_PORT3 ---> IIF(instr(column2,',',1,2)!=0,substr
(column2,instr(column2,',',1,2)+1,1),NULL)
Direct the variable ports to 3 output ports and this output
ports will go to normalizer. In normalizer create 2 ports
Column1 and Column2 and put the number of occurances for
column2 as 3.
The output will be 2 ports from normalizer which will be
feed to filter. In filter, filter out the null values in
column2 if it exists (IIF(ISNULL(Column2),FALSE,TRUE)
Direct the output of filter to target.
Hope this works !!!!!!!!!!
Revert Back if it works
Is This Answer Correct ? | 7 Yes | 1 No |
Post New Answer View All Answers
What does reusable transformation mean?
What are different types of transformations available in informatica?
How can we create index after completion of load process?
Which development components of informatica have the highest usage?
What are some examples of informatica etl programs?
How to use procedural logic inside infromatica?
Describe the impact of several join conditions and join order in a joiner transformation?
What is depict expression change?
what are factless facts? And in which scenario will you use such kinds of fact tables.
what is the size of u r source(like file or table)?
1)you have multiple source system where u receive files ,how do you actually load into mapping using transformation,what are the transformation you use? 2)you have files in ftp location ,how do you get it into mapping with you ETL concept?
What is session task and command task?
can anyone suggest best free Talend data integration training online
How will the document be delivered to me?
How do you update the records with or without using update strategy?