Subscribe Bookmark RSS Feed

Extracting Info from Comma Delimited Variable

mcstagger

Community Trekker

Joined:

Oct 6, 2014

Q: Is it possible to look within a comma delimited variable and extract information?

Context: I have a list of products (5) associated with a multi-punch variable and I want to look within the variable, see if the value associated with Product 4 (4) exists and if so, label this person as a user.

Example data:

IDProducts Used
2342412412,4,5

Using a simple IF(var==4,1,0) does not work. I think it is looking for a response that is perfectly in line with what is in the cell rather than looking the level existing within all the data.

Obviously I could reformat the data so that each product has it's own column, but I would like to know if there is a way around this with formula functions.

Thank you!

6 REPLIES
msharp

Super User

Joined:

Jul 28, 2015

If I understand right, use:

dt = current data table();

dt << New Column("Name", Character, Nominal, Formula( If( words(Column(dt, "Products Used")[], "," ) == 4, word(4,Column(dt, "Products Used")[], "," ))));


This adds a new column "Name" that checks column "Products Used" if it has exactly 4 items seperated by commas.  If it does it puts the fourth comma seperated item as the value of the column.

msharp

Super User

Joined:

Jul 28, 2015

Actually, I think you mean something different.

dt = current data table();

dt << New Column("Name", Character, Nominal, Formula( !IsMissing(regex(Column(dt, "Products Used")[], "4" )), "True", "False") ));

This uses regular expressions and searches column "Products Used" for a "4".  If there is a 4 it spits out True in the new column, otherwise it spits out "False"

mcstagger

Community Trekker

Joined:

Oct 6, 2014

I will test this out. Thank you.

Is this a formula that would work in the formula dialogue window when creating a new column?

msharp

Super User

Joined:

Jul 28, 2015

There was a small error in the code which I fixed above. 

But to answer your question, if you were to just copy and paste it in the formula field paste this:

If(!Is Missing(Regex(:Products Used, "4")), "True", "False")

The only difference is the Column portion, since you don't have to define your table.

ms

Super User

Joined:

Jun 23, 2011

Try the Contains()  function:

Contains(:Products Used, "4") > 0

chungwei

Staff

Joined:

Jun 23, 2011

If you tag your column as a multiple response column (i.e., add Multiple response property to the column), you can use the data filter to split out the different values.

e,g,

New Table( "test",

Add Rows( 10 ),

New Column( "Products",

Character,

"Nominal",

Set Property( "Multiple Response", Multiple Response( Separator( "," ) ) ),

Set Values(

{"1,2,4", "1,3,4", "2,3", "2,4", "1,2", "4", "1", "3", "1,4", "1"}

)

),

New Column( "User",

Numeric,

"Continuous",

Format( "Best", 12 ),

Set Values( [2, 3, 1, 3, 2, 4, 1, 3, 4, 4] )

)

);

Current Data Table() << Data Filter(

Location( {1166, 73} ),

Add Filter(

columns( :Products ),

Display( :Products, Size( 53, 77 ), Check Box Display )

)

);


Now, you can use the data filter to select the category of interest.
This may give you what you want.