Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Extracting specific columns into new dataset

Highlighted
8 - Asteroid

Hello,

I have a large matrix (as depicted below), and I would like to search each Cell-ID column for a specific gene value. If that value is met, I accept the whole column data and then move on to the next one. I.e. if [gene1732]  > 0.5 then return [Cell-ID1] else null endif.

Is there a way to return the whole columns data based on a single gene value?

 

Gene    Cell-ID1   Cell-ID2   Cell-ID3 ....Cell-ID34,766

1            -.00045   0.1702.    -.005

2

3

...

21,668

 

After sorting

Gene    Cell-ID751   Cell-ID1422   Cell-I1912 ....Cell-ID12,242

1

2

3

...

21,668

 

Thanks.

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

The easiest way to search would be to use the Transpose tool to transform your cell data into a single field to search. I would then use a Filter tool to get a list of the Cell-ID fields with values of interest. After that, it's a matter of updating a Dynamic Select tool with those names (plus the 'Gene' field).

 

If you don't want to do this in two steps, I've attached an example workflow that uses a batch macro to update the Dynamic Select tool.

Highlighted
8 - Asteroid

I am not familiar with the dynamic tool, but it looks like exactly what I need. Let me see if I can get this going. Thanks

Highlighted
8 - Asteroid

This tool capability is perfect, but I am having some trouble with it. First, I can't seem to find this specific tool in the developer section of my Alteryx (I am running Alteryx 2018.3.5). When I copy your Dynamic Select Name tool to use in my workflow, I get a "#1 parse error at char(18): Unknown variable "TAAACCGAGAAGGACA". Cant seem to get around that. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Could you share the workflow/data that's producing that error?

Highlighted
8 - Asteroid

I have attached the workflow, but the data files I am working with are around 1-2GB. I have taken a few screenshots though if that helps.

Highlighted
8 - Asteroid

Also, how did you make a Dynamic Select Tool with 2 input capabilities? When I take the one from my developer section it only has 1.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

The standard Dynamic Select tool in the Developer section does have 1 input and 1 output anchor. I made a custom batch macro that updates this tool (which requires a second Input anchor) so it's that custom macro you see with two anchors. I used the background icon image from the Developer tools to correspond to the macro's functionality.

 

Thanks for sharing the workflow, but without data, I'm not able to troubleshoot much more. How about a data sample with just the first few rows? If you can put that into Text inputs the workflow could be shared again. If they're Excel files, you'll need to Export the workflow as a yxzp using Options > Export Workflow.

Highlighted
8 - Asteroid

Hi CharlieS,

Please see attached sample from the data files. Essentially, I am searching the transposed list for all cell IDs (Name) that match the cell ID in the filtered list. I think that should pull out the entire data set from he column of cells of interest.

Highlighted
8 - Asteroid

Hi CharlieS,

Please see attached sample from the data files. Essentially, I am searching the transposed list for all cell IDs (Name) that match the cell ID in the filtered list. I think that should pull out the entire data set from he column of cells of interest.

Labels