topic Re: Mutli row formula or Batch macro in Alteryx Designer Desktop Discussions

Mutli row formula or Batch macro

Shank — Tue, 03 May 2022 12:10:03 GMT

Dear All,

I am facing some challenge to concatenate rows based on the column id can some one help on how can we achieve this?

below is the scenario.

Example: I have a part 1 which goes from line 2022 - 2060 and from 2060 - 1692 (But i have this information in two different columns). I need an output to show line information in one row like 2022-2060-1692 for part 1. can anyone help us on this?

General example to understand the concept:- in order to reach location C from location A. I have to travel location A to B first then from B to C

A-B-C

Input:

Part	Line from	Line To
1	2022	2060
1	2060	1692
2	1849	2022
2	1842	1849
3	1692	1699
3	1699	2022
3	2022	2025

Expected Output:

Part	Line Info
1	2022-2060-1692
2	1842-1849-2022
3	1692-1699-2022-2025

Best Regards,

Shashank

Re: Mutli row formula or Batch macro

IraWatt — Tue, 03 May 2022 12:19:39 GMT

Hey @Shank,

You can use a multi row formula and summary tool like this to achieve this (workflow attached below):

If you have any questions on how this works make sure to ask :)

HTH,

Ira

Re: Mutli row formula or Batch macro

mceleavey — Tue, 03 May 2022 12:36:35 GMT

@Shank ,

Isn't this a question on one of the exams?

Re: Mutli row formula or Batch macro

Shank — Tue, 03 May 2022 17:55:47 GMT

@mceleavey

Nothing as such! I was just trying to solve but I could not achieve it. so thought of posting it in the forum, so that I can learn something out of it :) .

Thanks for your feedback :)

Re: Mutli row formula or Batch macro

Shank — Tue, 03 May 2022 16:53:46 GMT

@IraWatt Thanks for helping me on this one.

I have one concern here for part 2, with the logic that you have used output achieved is 1849-2022-1842-1849.

but i actually need this 1842-1849-2022.

Re: Mutli row formula or Batch macro

IraWatt — Tue, 03 May 2022 17:35:37 GMT

Hey @Shank,

What logic is that following? Part one of journey two is 1849->2022 then part two is 1842->1849. If we are removing duplicates would it not be 1849-2022-1842 ?

Re: Mutli row formula or Batch macro

IraWatt — Tue, 03 May 2022 17:37:48 GMT

@mceleavey trying to call people out XD

Re: Mutli row formula or Batch macro

Shank — Tue, 03 May 2022 17:50:50 GMT

Just to give simple example

i have to create a transportation line to reach final destination. the ultimate goal of part 2 is to reach 2022 but this can happen only by travelling from 1842 to 1849 then from 1849 to 2022.

in above case 1849 is the common.

therefore output should be 1842-1849-2022

hope this helps!

Re: Mutli row formula or Batch macro

Shank — Tue, 03 May 2022 17:51:55 GMT

@IraWatt

Just to give simple example

i have to create a transportation line to reach final destination. the ultimate goal of part 2 is to reach 2022 but this can happen only by travelling from 1842 to 1849 then from 1849 to 2022.

in above case 1849 is the common.

therefore output should be 1842-1849-2022

hope this helps!

Re: Mutli row formula or Batch macro

IraWatt — Tue, 03 May 2022 18:12:07 GMT

@Shank how do you know from looking at the table that 2022 is the end goal? The other examples are ordered from left to right the move down, the end goal is always in the right cell of the last part of the journey.

Re: Mutli row formula or Batch macro

Shank — Tue, 03 May 2022 18:23:58 GMT

@IraWatt

There is no link which is available to connect from 2022 to anywhere else that is the reason end destination is 2022. In above example if we had a continuity like from 2022 to 2099. then our end goal will be 2099.

Yes you are right the end goal is always in the right cell of the last part of the journey.

Re: Mutli row formula or Batch macro

LaszloMegyeri — Tue, 03 May 2022 19:03:54 GMT

Hi @Shank,

Maybe this example helps you.