How to achieve this kinda transpose?

[Anonymous]

Hi All, 

Here is my input and output, i'm not sure how to achieve this kind of output.

My source is an excel file, is there a way i can read specific field in an excel?

Input: 

		Store1
		2001	5003	4002	703
Item	Code	Price1	Price2	Price3	Price4
Oranges	1	100	120	130	140
Apples	2	180	190	195	200

Output : 

Item	Code	Range	Store_Code	Store_Name	Amount
Oranges	1	Price1	2001	Store1	100
Oranges	1	Price2	5003	Store1	120
Oranges	1	Price3	4002	Store1	130
Oranges	1	Price4	703	Store1	140
Apples	2	Price1	2001	Store1	180
Apples	2	Price2	5003	Store1	190
Apples	2	Price3	4002	Store1	195
Apples	2	Price4	703	Store1	200

Appreciate your help.

Thanks

[nfz11]

Take a look at this article

https://help.talend.com/reader/Vya20tI3CbK3zEk~JdFPew/9rU5DFWIl4v9v8OwMVnu4Q

What you are trying to do is called a pivot or unpivot.

[Anonymous]

Thanks for the reply.

But if you notice closely, we have another column to generate for store and store_code.

Which is the tricky part. 

And is there where we can read excel data from specific cell?

Cheers!

[Anonymous]

Can anyone help me with the same?

[nfz11]

Look at the *Excel* components.  This doc might help you:

https://community.talend.com/t5/Design-and-Development/how-to-extract-a-value-of-a-cell-in-the-excel...