Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Selecting specific cells based on condition

Highlighted
7 - Meteor

Is it possible to select cells in a column based on the following condition: 

 

If a date has a value above 0, keep the cell

Else remove cell

 

Like below as you can see, I would only want to save the dates where there are hours input (ie. 7.5)

 

daraghwalker_0-1587721781661.png

 

 

The output should look like below:

 

daraghwalker_1-1587720799313.png

 

 

Highlighted
14 - Magnetar

Hi @daraghwalker,

 

you can use a Filter-Tool to achieve this.

 

grossal_0-1587721948756.png

 

Best

Alex

Highlighted
7 - Meteor

Hi @grossal,

 

Thanks for your response, although this does not seem to give me the desired output.

 

For example, if I try filter out Monday values that is not equal to zero, I get the following:

 

daraghwalker_0-1587725571381.png

 

I am assuming one column must work before I try and filter all columns?

 

Thanks!

Highlighted
14 - Magnetar

Ohh sorry @daraghwalker.

 

I only had quit look at the expected output and saw you reduced them to two rows and that's it.

 

Where do all the columns come from in your expected output? Your Input Sample does not show these rows. Please provide a full sample to make it easier to understand.

 

Best

Alex

Highlighted
14 - Magnetar

If you want the first and last non-null value of every row, you could the following:

 

- Add RecordID

- Transpose all Weekdays

- Filter Null value

- Use two Sample Tools (parallel) with Group by option on Record ID (First 1 row, Last 1 row)

- Join everything back together with two Join Tools or one Join Multiple with Key on Record ID

- Remove RecordID in the end

Highlighted
7 - Meteor

Hi @grossal, thanks once again for your reply. I wasn't clear enough in my first post so I have included a rough sample of what my full dataset looks like at the moment, and included my desired output in the workflow below. Hopefully, you get a clear picture of what I am trying to get from this.

 

Thanks!

Highlighted
14 - Magnetar

Hi @daraghwalker,

 

you missed to include the Excel files, therefore the workflow is kinda empty.

 

grossal_1-1588150080926.png

 

You can easily do this going to the Options and clicking on "Export Workflow". This allows you to create a YXZP file with all content you want in it.

 

 

grossal_0-1588150055439.png

 

An alternative would be uploading the Excel-files. 

 

 

Best

Alex

Highlighted
7 - Meteor

Apologies!

Highlighted
14 - Magnetar

I think I got you!

 

grossal_4-1588178772836.png

 

Output:

grossal_3-1588178764805.png

 

 

What happens:

1) Removing columns that aren't needed (Select)

2) Removing all entries without Payroll number (Filter)

3) Transposing the value columns into rows (Transpose)

 

4) Text Input with custom conversion for the weekdays, these will come in handy when we try to get start / end date (Text Input)

grossal_2-1588178534964.png

 

5) Join both together (Join)

6) Formula with Starting-Week plus "Apply" value to calculate the date of an entry (Formula)

7) Summarize to find out "Max/Min" date (Start/End) per Payroll Number (Summarize)

Formula Tool to add AM / PM Columns (Formula)

9) Ordering everything by Start Date (Sort)

10) Select to fix the column order (Select)

 

Workflow attached. Let me know if I got it right.

 

Best

Alex 

Highlighted
7 - Meteor

This definitely works for me! Many thanks, appreciate it!

 

Regards

Daragh

Labels