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
Solved! Go to Solution.
Hi @JohnnycsLeung. Is this what you are looking for?
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
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
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)
Part time Tableau, Part Time Alteryx. Full Time Awesome
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
How peculiar!
Sometimes i find using the Excel Legacy connection resolves some oddities.
Neil