Selecting specific cells based on condition

[daraghwalker]

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)

The output should look like below:

[grossal]

Hi @daraghwalker,

you can use a Filter-Tool to achieve this.

Best

Alex

[daraghwalker]

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:

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

Thanks!

[grossal]

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]

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]

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]

Hi @daraghwalker,

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

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.

An alternative would be uploading the Excel-files. 

Best

Alex

[daraghwalker]

Apologies!

[grossal · Accepted solution]

I think I got you!

Output:

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)

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]

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

Regards

Daragh