Extracting Info from Comma Delimited Variable

[#1]

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:

ID	Products Used
234241241	2,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!

[#2]

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.

[#3]

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"

[#4]

I will test this out. Thank you.

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

[#5]

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.

[#6]

Try the Contains()  function:

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

[#7]

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.