Hi All I am new to Alteryx and having a problem with the following ... I have 2 Excel data files look like below: File 1 DateCompanyACCcyBalance17/04/2018A1111USD100017/04/2018A2222USD100018/04/2018A8888USD50018/04/2018AA9999USD-400 File 2 CompanyACCcy17/04/201818/04/2018BA9999USD-2006000B1111USD5000-3000 I want to transpose the File 2 as of the format of File 1 and union them together. But it seems that simple transpose will not work as part of the data is in the title row of File 2 (date). Also, whenever I set file 2 as an input, those dates will be shown as "F3","F4", etc Any simple way to sort it out? Thanks Johnny

Transpose with some data in the title row

Hi All

I am new to Alteryx and having a problem with the following ...

I have 2 Excel data files look like below:

File 1

Date	Company	AC	Ccy	Balance
17/04/2018	A	1111	USD	1000
17/04/2018	A	2222	USD	1000
18/04/2018	A	8888	USD	500
18/04/2018	A	A9999	USD	-400

File 2

Company	AC	Ccy	17/04/2018	18/04/2018
B	A9999	USD	-200	6000
B	1111	USD	5000	-3000

I want to transpose the File 2 as of the format of File 1 and union them together.

But it seems that simple transpose will not work as part of the data is in the title row of File 2 (date).

Also, whenever I set file 2 as an input, those dates will be shown as "F3","F4", etc

Any simple way to sort it out?

Thanks

Johnny

Input

Transformation

Accepted answers

bharti_dalal

Hi @JohnnycsLeung,

I am attaching your version worklow i.e. 11.3.2. See if u need that.

Transpose.yxmd

LordNeilLord

How peculiar!

Sometimes i find using the Excel Legacy connection resolves some oddities.

Neil

All comments

LordNeilLord

Hey @JohnnycsLeung

On the transpose tool you need to make sure you have grouped by company, Ac & Ccy... This will give you the same format as file 1.

I'm not sure about the header issue, I would need to have an example spreadsheet to diagnose that one

Neil

bharti_dalal

Hi @JohnnycsLeung. Is this what you are looking for?

Screenshot (161).png

Transpose.yxzp

JohnnycsLeung

Hi Bharti

Thanks for your prompt reply.

But ... I can't run the imported yxmd ..... since my version is older (11.3.2.29874) and think I can't upgrade it due to company policy on software administration access control....

So how I can do the grouping in transpose?

(I am very new to Alteryx)

Thanks

Johnny

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335