Make one column of data into one quoted, comma separated field

[Archaeopteryx]

Hi,

I'm running an API where I provide UserIDs as quoted, comma separated values. Like this:

{"userIds":["12345-67890-54321-54321","67583-02751-22331-54545","21234-00900-44567-13135","...",",,,",".."]}, and on and on.

The API in question generates a User Status Detail Report, which is a canned report within the Admin Portal that carries its own ID. 

First I run a stored procedure to fetch all the users from the database table. I then "Select" the UserID column all by itself. Now for the tough part - and you guys may be able to show me a better way, but ...

I would like to take that UserID column, and make it into one field whos IDs are quoted and comma separated. I would pass that field into an Alteryx formula that is the payload API for the Download tool. 

Is this possible? Thank you for any assistance.

Chris

[atcodedog05]

Hi @Archaeopteryx 

Here is how you can do it.

Workflow:

1. Using formula to add separator and keep only the required string.

2. Using text to columns to split to rows.

Hope this helps : )

[apathetichell]

the user-ids come into the database table like "12345-67890-54321-54321" or with no quotes? with quotes you'd use a summarize tool set to concatenate your user id:

prefix:

{"userIds":{

separator:

,

suffix:

]}

if you don't have quotes coming in with the values try:

prefix:

{"userIds":{"

separator:

","

suffix:

"]}

Is that kind of what you are looking for?

[Archaeopteryx]

Hi,

Thank you for your assistance. Please see attached column of UserIDs.

I want to take that column and make one field like this:

UserIDs

"12345-09876-12345","09875-35465-03625","23453-00098-36363","46296-63658-093658","462144-824264-9264758","22334-99449-535353"

I can handle the API part. I just need the data to do that above in one column so I can regard it as all one field of data. 

[atcodedog05]

Hi @Archaeopteryx 

You can use formula like below.

REGEX_Replace([Field1], '{"userIds":\[(.+)\]}', "$1")

Workflow:

Hope this helps : )

[apathetichell]

follow my instructions for the summarize tool and just trim the prefix and suffix parts you will use in the download tool directly... so try it with " as prefix "," as separator and " as suffix

[atcodedog05]

Hi @Archaeopteryx 

I am really getting confused what your input looks like. Can you share sample of input data is it single line or mult-line ?

[Archaeopteryx]

Hi,

The UserID data comes in like this:

UserID

7474747487-7474747474-26526262626

9898974747-654647696969-737377373

9797979797-73272727272-4534343131

75643875764-4376584364758-52525252

00000-433333-00000-33333-838383

I want to transform all those IDs into one field, quoted, comma separated like this:

UserID

"8585885-4994994-494949","838383283-329398329932-3292939","838383-8328282-3292929","9329292-2929292-0929292

[atcodedog05 · Accepted solution]

Hi @Archaeopteryx 

Configure like below.

Hope this helps : )

[apathetichell]

@atcodedog05a.k.a. what I said. twice.