How to achieve this kinda transpose?

Highlighted
Six Stars

How to achieve this kinda transpose?

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
  200150034002703
ItemCodePrice1Price2Price3Price4
Oranges1100120130140
Apples2180190195200

 

Output : 

ItemCodeRangeStore_CodeStore_NameAmount
Oranges1Price12001Store1100
Oranges1Price25003Store1120
Oranges1Price34002Store1130
Oranges1Price4703Store1140
Apples2Price12001Store1180
Apples2Price25003Store1190
Apples2Price34002Store1195
Apples2Price4703Store1200

 

Appreciate your help.

Thanks

Nine Stars

Re: How to achieve this kinda transpose?

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.

--
Please give Kudos and mark topics as solved where appropriate.
Six Stars

Re: How to achieve this kinda transpose?

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!

Six Stars

Re: How to achieve this kinda transpose?

Can anyone help me with the same?

Nine Stars

Re: How to achieve this kinda transpose?

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...

--
Please give Kudos and mark topics as solved where appropriate.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Introduction to Talend Open Studio for Data Quality

Find out about Talend Open Studio for Data Quality

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Enabling Data Governance

Learn how to enable Data Governance

Watch Now