Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Selecting specific cells based on condition

daraghwalker
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

 

 

9 REPLIES 9
grossal
15 - Aurora
15 - Aurora

Hi @daraghwalker,

 

you can use a Filter-Tool to achieve this.

 

grossal_0-1587721948756.png

 

Best

Alex

daraghwalker
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!

grossal
15 - Aurora
15 - Aurora

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

grossal
15 - Aurora
15 - Aurora

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

daraghwalker
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!

grossal
15 - Aurora
15 - Aurora

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

daraghwalker
7 - Meteor

Apologies!

grossal
15 - Aurora
15 - Aurora

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 

daraghwalker
7 - Meteor

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

 

Regards

Daragh

Labels