How to select rows between comma seperated values consisting column variable

Question

Hi,

I need to select those rows which includes "EB1", EB2", "EB3" and "EB4" in column "Packets". How to script it?

Thanks in advance

saitcopuroglu · Answer

Thank you ChungWei,

Thank you jvillaumie.

Both the solutions are correct for my purposes, I realised the formula (which I did not know before what the contain character formula is:

If(

Contains( :Packets, "EB 1" ), "EB1",

Contains( :Packets, "EB 2" ), "EB2",

Contains( :Packets, "EB 3" ), "EB3",

Contains( :Packets, "EB 4" ), "EB4",

Contains( :Packets, "EB 5" ), "EB5",

"No EB"

)

gave me what I needed by creating a column called "EB".

However the solution of marking as multiple response column is absolutely amazing but i guess I need further support of how to:

With the formula option I have this result which is not. What to do further to achieve same as above (with local data filter)?

jvillaumie · Answer

You could script to add a column looking for the presence of your text of interest (EB 1 or EB 2 or EB 3 or EB 4), then select where that text is present, and delete then column to keep things tidy:

dt = current data table();

dt << new column("Has EB", formula (If( Contains( :Packets, "EB 1" ) | Contains( :Packets, "EB 2" ) | Contains( :Packets, "EB 3" ) | Contains( :Packets, "EB 4" ), "Yes, has those EB", "Does not have those EB")));

dt << SELECT WHERE (:Has EB == "Yes, has those EB");

dt << Delete column(:"Has EB");

Note that the above script will select exclusively EB 1, 2, 3, and 4, but not 5 or 6 and so on. The non specific version is simpler:

dt = current data table();

dt << new column("Has EB", formula (If( Contains( :Packets, "EB" ), "Yes, has EB", "Does not have EB")));

dt << SELECT WHERE (:Has EB == "Yes, has EB");

dt << Delete column(:"Has EB");

(edit: could not quite see if there is a space between EB and the number or not in your table. I assumed so. Delete the spaces in the 1st script if required. Makes no difference for the 2nd script).

chungwei · Answer

You can mark your column Packets as a multiple response column (use Column Info dialog to assign the Multiple response column property, with comma as the separator).

Then you can use data filter to select the different responses.