How to achive this scenario on talend ETL

[Anonymous]

Hi ,

we have the source data like below

custId|Name|item_no

100|A|4

200|B|3

300|C|5

Expected Output is each row generate item_no value of times.

CustId|Name

100|A

100|A

100|A

100|A

200|B

200|B

200|B

300|C

300|C

300|C

300|C

300|C

Thanks

[Anonymous]

Hello,

i succeed with this scenario ,see this example by using MSSQL

input:

OwnerNumber ItemCode    ItemNumber  CountOfItems
1234    Item1   Item1-001   3
1234    Item1   Item1-002   1
1234    Item1   Item1-003   2
1234    Item2   Item2-001   1

output:- 

OwnerNumber ItemCode ItemNumber

1234     Item1 Item1-001

1234     Item1 Item1-001

1234     Item1 Item1-001

1234     Item1 Item1-002

1234     Item1 Item1-003

1234     Item1 Item1-003

1234     Item2 Item2-001

 by using this query :- 

WITH CTE AS (

    SELECT OwnerNumber,ItemCode,ItemNumber,CountOfItems FROM table

    UNION ALL SELECT OwnerNumber,ItemCode,ItemNumber,CountOfItems-1

    FROM CTE

    WHERE CountOfItems >= 2

)

SELECT OwnerNumber,ItemCode,ItemNumber

FROM CTE

ORDER BY ItemNumber

OPTION (MAXRECURSION 0);

note:- In ETL firstly you have to 1]load your input data to ms sql db

2] import tmssqlinput as source component,

3] put the query  in query section of tmssqlinput

4] load it to output db...

Hope it will help you....

thanks,

Warm Regards,
Manish

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Hi, thank you, data coming from source in a flat file and also how to implement other database like oracle, mySql

[Anonymous]

hello,

1] import flat file as source and load the data into tmysqloutput-1,

2]again import tmysqloutput -1 as source tmysqlinput-1

3]in tmysqlinput-1 setting  query section put the query and connect it with tmysqloutput-2

4]run the job ..

for more details see attached screenshots

NOTE -: CTE function working with only mysql latest version first try it to your database then implement with TOS

thanks,

Warm Regards,
Manish

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

N1.JPG
n2.JPG
N3.JPG
N4.JPG

[manodwhb]

@jpreddym,check the below solution work for your use case.

[Anonymous]

thanks a lot

[manodwhb · Accepted solution]

@jpreddym ,Please accept the solution.