Mapping a Financial Statement

[ayalisrael16]

Hello, 

I am trying to see if I can mapp a financial statement with two files . In the first file I have the account and ammount of the trial balance. And in the second file I have the account totals. 

File 1 

Account     Amount 

100001      650.12

100002      650.00

100003      200.00

100004      200.00

100005      200.59

File 2 

Account-EEFF         Amount 

Cash                       1500.00

Inventory                   401.00

Is there a way to check all the amounts and have Alteryx in the output set the account as the account in the financial statement. So I know the mapping is correct. I need the output to be like the next information : 

Account-EFFF      Amount 

Cash

100001                  650.12

100002                  650.00

100003                  200.00

Total                      1500.00

Inventory 

100004                 200.00 

100005                 200.59

Total                     401.00

[Prometheus]

@ayalisrael16 If File 2 has values in a field that match the values in a field in File 1, you'd be able to use the Join tool. However, based on the sample of data you are showing, you'd first have to classify what accounts in File 2 are Cash and which are Inventory, then you could join the two on Account, or you could classify which accounts in File 1 are Cash and Inventory, then join on Accounts. Either way works.

[ayalisrael16]

Thank you for the reply, is there a way to Alteryx automatically sum the accounts in file one and put the result with the total of cash of file 2. And then give me the answer ?

[Luqman]

Newbie here so this might too much of a workaround

You can create a whole new template first listing your preferred output

1) Use summarize tool to prepare your file 1

2) Create a template of your output, then use find/replace to key in your new file(Vlookup).. The select tool is just there because Find/Replace needs v-string data

3) Match it with File 2

Attached is a workflow for step 2)

[ayalisrael16]

Thanks for the reply, I do not know from the file I have what account is mapped to what account . 

I have the first file with the accounts and amounts and then I have the second account with the total of each mapping group. 

Is there a way to sum it so it will tell me wich account is mapped by the total of the second file ?