topic Re: Tunpivot -- without using unpivot component in Talend Studio

Tunpivot -- without using unpivot component

Anonymous — Sat, 16 Nov 2024 03:57:57 GMT

Hi team,

I am using licensed version 7.2 , I tried to download unpivot component and upload from exchange , but my tool is not supporting then I tried to do below method

source(db)-->transformer -->tnormalise-->trgtDb

in tmpa - --row6.Apple+";"+row6.Banana+";"+row6.Kiwi

input

date	cntry	Apple	kiwi	orange
12/11/2019	inida	10	20	11
12/12/2019	uk	110	30	44
12/13/2019	us	210	40	33
12/14/2019	malaysia	10	60	66

output

Date , country , Derived value , profit

10-10-2019 , india , apple, 10

10-10-2019 india , banana,20

10-10-2019 india , kiwi ,30

10-11-2019,uk,Apple,20

10-11-2019,uk,banana,30

10-11-2019,uk,kiwi,40

ana

in derived column I need column name like banana , kiwi

Re: Tunpivot -- without using unpivot component

Anonymous — Tue, 03 Dec 2019 09:29:00 GMT

Hello,

Could you please elaborate your case with an example with expected output values?

Best regards

Sabrina

Re: Tunpivot -- without using unpivot component

manodwhb — Wed, 04 Dec 2019 09:41:57 GMT

@Moe , you can create something in the below expression in tMap or tJavarow and write it to a file. so will get the required output.

row1.date+","+row1.cntry+","+"apple"+row.Apple+"\n"+

row1.date+","+row1.cntry+","+"kiwi"+row.kiwi+"\n"+

row1.date+","+row1.cntry+","+"orange"+row.orange

Re: Tunpivot -- without using unpivot component

Anonymous — Mon, 09 Dec 2019 05:39:52 GMT

Hi All,

To do unpivot when we are using enterprise edition there two option .

1. tsplitrow ( when you have 4 to 5 column ) it will work exactly the way Unpivot sql function will work.

2. SQL when you have 280 or 300 column values)

SELECT EDM_KEY,EDM_CREATED_DATE,EDM_LASTMODIFIED_DATE,SRC_FILE_NAME,YEAR_MONTH_DAY,YEAR,MONTH,mm_CUSTOMER_IDENTIFIER,mm_PRODUCT_IDENTIFIER,'MM' AS SOURCE_FLAG ,'Cust_Value' AS DRIVER_OBJECT_PROFIT, Cust_Value AS PROFIT_VALUE FROM Cust WHERE Cust_Value<> 0 UNION ALL

like this write 280 or 300 stamen using excel .. and notepad++.

you can achieve

Tmap -- has limitation -- you wont get derived value (column name )

thanks

Mohini

Re: Tunpivot -- without using unpivot component

manodwhb — Mon, 09 Dec 2019 05:47:21 GMT

@Moe , you can do with tMap the same way you need to write a big expression as I gave in above.