Transpose with some data in the title row

[JohnnycsLeung]

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

[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? 

[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

[JohnnycsLeung]

Hi Bharti

I just found that as long as I select the correct Key fields (Company, AC, etc) and data fields (those dates) in transpose, it works.

Thank you so much for you help.

Now the problem left is that those dates become "F4", "F5".....

May be I try to resave as csv and see if it works

Rgds

Johnny

[LordNeilLord]

Hey @JohnnycsLeung

Sorry, I should have been clearer...they key fields are the same as grouping fields (just called different in some tools) :)

You shouldn't need to re-save as csv, Alteryx will have no issue with an excel. If you can attach an example we can have a little play and find out why it's not reading correctly (for example there may be merged cells in you file)

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[JohnnycsLeung]

Hi LordNeilLord

Sorry about that I missed your reply and you actually suggested the solution :D

Actually I found something funny:

--. The source file is from a SQL server which generated as Excel 2003 version.

--. My PC is installed with Excel 2013.

--. If I added it as input, the problem came out.

--. If I resaved it as CSV, the problem went away.

--. I manually created the data file from scratch and saved it as Excel 2013 format, no issue when added it as input.

So it seems to me that the issue is the source file version does not match the Excel installed .....

I am still trying if there is any working around.. any good suggestion?

(And sorry... I can't upload the sample file for the moment)

Rgds

Johnny

[bharti_dalal · Accepted solution]

Hi @JohnnycsLeung,

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

[LordNeilLord · Accepted solution]

How peculiar!

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

Neil