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

Dynamic Input (with few rules)

mohit9garg
8 - Asteroid

Hi,

 

I need your help to construct a Dynamic Input tool with few rules & conditions.

 

  • In a sharedrive path (a fixed directory)
  • we would have a folder (somewhere there) for last working day and that date would be the folder name; for example, if today's date is 25th May then folder name would be 20200522. But date format is not fixed.
  • the file name would have 'xyz' but should not have 'pqr'; file name would also have the folder date. like 20200522; but again date format is not fixed.
  • File format could be any excel format (.xls , .xlsb , .xlsx..)

Then workflow should process that file.

 

Kindly guide me.

 

Mohit

 

@grossal @DavidP 

 

 

12 REPLIES 12
grossal
15 - Aurora
15 - Aurora

Hi @mohit9garg,

 

I think the first thing you need is the directory tool for the shared drive - make sure to Include SubDirectories.

 

grossal_0-1590441922402.png

 

After this we can use a Filter-Tool to figure out the right file. The filter probably needs something like:

Contains([FileName], Replace(DateTimeToday(),'-','')) 
AND NOT Contains([FileName],'second condition')

 

You could pass this information into a Batch Macro that reads in the file. This should look like:

 

grossal_1-1590442207350.png

 

 

 

I would probably tick "Auto Configure by Name" - just in case:

 

grossal_2-1590442263747.png

 

The main workflow would look like this:

grossal_3-1590442376296.png

 

 

Let me know if this helps. I won't be able to build an exact workflow because I don't have access to your drive etc, but this should give you a good idea how to continue.

 

Best

Alex

 

danilang
19 - Altair
19 - Altair

Hi @mohit9garg 

 

@grossal gave you a great start on this.  The biggest remaining issue is your statement that says "the date format is not fixed"  The suggested solution gives you the date part of the directory and file names in yyyymmdd format.  Which other formats are possible?  If the format isn't fixed, how do you handle ambiguous dates?  20200102 could be Jan 02 or Feb 01

 

Dan

mohit9garg
8 - Asteroid

Hi @danilang - Thanks for noticing. I am putting this ask aside for the time being. Let me try to build a workflow with fewer conditions. 🙂

 

Thanks @grossal for the solution. But could you please advise how could I achieve below-

 

  • we would have a folder (somewhere there) for last working day and that date would be the folder name; for example, if today's date is 25th May then folder name would be 20200522. But date format is not fixed.

I need to find the folder fist in the main Directory which contains the date of last working day; which is in yyyymmdd format. Because actual excel file won't have the date.

 

Thanks,

Mohit 

 

 

 

grossal
15 - Aurora
15 - Aurora

Hi @mohit9garg,

 

I'd do this in a three step process:

 

grossal_0-1590861738535.png

 

What happens:

1) I create a helper column with the Weekday as a String

2) We correct the date for weekends

3) We create the string in your format.

 

I'll post the formulas below for easy copy paste.

 

//First formula
DateTimeFormat([Date],'%A')

//Second formula
IF [Weekday] = 'Saturday'
THEN DateTimeAdd([Date],-1,'days')
ELSEIF [Weekday] = 'Sunday'
THEN DateTimeAdd([Date],-2,'days')
ELSE [Date]
ENDIF

//Third formula
DateTimeFormat([Date],'%Y%m%d')

 

Best

Alex 

mohit9garg
8 - Asteroid

Thanks @grossal for your prompt response.

 

To get the last working day, I think our formula should be like-

 

If Today ="Monday"

Then [Date}-3

ElseIf Today ="Sunday"

Then [Date}-2

Else [Date]-1

 

To get the required folder Should I do like this (please excuse my ignorance)

 

mohit9garg_0-1590862614538.png

 

mohit9garg
8 - Asteroid

@grossal @danilang  Could you please help me here.

grossal
15 - Aurora
15 - Aurora

Hi @mohit9garg,

 

sorry for the late response.

 

Just add another Formula Window add the top, name the column 'Date' and use the Formula DateTimeToday(). Make sure the Type is set to Date.

 

I am not quite sure why you would want that Monday is replaced with Friday or I understand you wrong. I thought you'd want to always have the last workday based on the current date. Therefore it should look like this:

 

Current DayLast Workday
MondayMonday
TuesdayTuesday
WednesdayWednesday
ThursdayThursday
FridayFriday
SaturdayFriday
SundayFriday

 

 

Best

Alex

mohit9garg
8 - Asteroid

Hi Alex @grossal 

 

Hope you are doing good. Thank you (as always) to look into my query.

 

I mean with last working days as below-

 

Today's dayLast Working Day
MondayFriday
TuesdayMonday
WednesdayTuesday
ThursdayWednesday
FridayThursday
SaturdayFriday
SundayFriday

 

Could you please build a workflow for me as I am getting the errors.

 

Regards,

Mohit

grossal
15 - Aurora
15 - Aurora

here is a minimal example: 

 

grossal_0-1592766758033.png

 

Formula:

grossal_1-1592766775259.png

 

Result:

grossal_2-1592766808531.png

 

Workflow attached.

 

Best

Alex

 

 

 

 

 

 

Labels