Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
saitcopuroglu
Level IV

How to select rows between comma seperated values consisting column variable

Hi,

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

Thanks in advance

9335_2015-07-28_12-36-50.png

3 REPLIES 3
Highlighted
chungwei
Staff

Re: How to select rows between comma seperated values consisting column variable

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.


Highlighted
jvillaumie
Level III

Re: How to select rows between comma seperated values consisting column variable

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).

Highlighted
saitcopuroglu
Level IV

Re: How to select rows between comma seperated values consisting column variable

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".

9342_2015-07-30_17-30-45.png


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)?


9343_2015-07-30_17-24-19.png



Article Labels

    There are no labels assigned to this post.