Informatica Interview Scenario :Listagg Function in Informatica

A source table has values
Col1 Col2
a l
a m
a n
b p
b q
x y
The target table should have values
Col1 Col2
a l,m,n
b p,q
x y
The Video Demonstrates 2 approaches to achieve the same using listagg function in SQ query and by using expression and aggregation in case of flat file. Please post in comments section in case you want help with any scenario i will try to cover it in my next video
#InformaticaPowercenter #Listaggfunction#Techcoach

[@AnandRajakrishnan]

This mapping worked because the input data came in a desired way. All the rows with same COL1 were processed one after the other. This may not be the case every time. A sorter should be put before the expression to avoid this error.

[@Jojms123]

This was an amazing explannation thank you so much for this. In the beginning I thought you were just using Sql override but then .... LIGHT came into my life :)

[@TechCoach]

I am glad I could help jorge :)

[@KuldipSingh-so8dz]

This is very helpful Vivek bro. I got particular this scenario in one of the interview. Thank you very much bro. plz keep uploading...

[@TechCoach]

Thanks a lot, I am happy it helped.

[@jagdishk6307]

good explanation... keep update....

[@pritamswarupajena1194]

U shd use sorter before expression

[@SreeramReddyB]

What if we don’t know how many records are there in each group ? Giving a very high value for precision of output column is the only way ?

[@galiramanaji7625]

Starts from 6:00 for flatfile

[@bratisankarchakraborty2742]

While implementing this scenario,only the concat operator is working

[@TechCoach]

Can you please elaborate a little

[@silaswilfred]

Great explanation bro.. Please do a video on push down optimization in Informatica.

[@TechCoach]

Thanks a lot for the kind words. I would really appreciate if you help me in my new Initiative "An Indian Abroad" by subscribing viewing and sharing. kzbin.info

[@Telugufun427]

ur listagg query is not executing getting error like from not found where expected i dont understand for you how it was executed

[@TechCoach]

Can you share the query , you executed

[@Telugufun427]

@@TechCoach select col1, listagg(col2, ',') within group (order by col2) as col2 from tablename group by col1

[@ztech4634]

if the requirement is to concatenate more than 2 columns can we still use the LISTAGG function?

[@TechCoach]

It can be done but Listagg is used to create a list which you would usually involve a single column

[@ztech4634]

Hi, can I have your e_mail address. thanks

[@TechCoach]

@@ztech4634 [e-mail]

[@suryachekuri6551]

Hi bro, the concatenation method in expression transformation doesn't working for me. Mapping was successful but I couldn't get the actual output. Could you help me please.

[@TechCoach]

Hi Surya, Can you post the screenshots of your logic here

[@mbera.2011]

Thanks

[@TechCoach]

I am glad I could help

[@vikrantmukkawar9737]

Nice video keep it up one question here can we pass multiple column to listagg function ?

[@TechCoach]

@ vikrant, yes we can we just need to use concatenate function. Also Thanks for the kind words :) Please consider subscribing to my other channel as well. It will really help me. kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@vikrantmukkawar9737]

@@TechCoach ok but I guess listagg have only 4000 character limit , I want to convert relational data ( array data) to json using java transformation can we do it with java transformation ?

[@dilipreddy6434]

sir could please upload more scenarios so that we would gain more knowledge. exquisite explanation, i have really impressed with your scenarios , it is really wrathful.thank you

[@TechCoach]

dilip reddy Sure Dilip, I will try to upload more :)

[@dilipreddy6434]

sir, you will give personal training for informatica ??if yes please provide your details @[e-mail]

[@ztech4634]

Under what condition would you consider using views instead of tables during ETL process? Could I have your email address?