Analytical application - list box

[olimpio]

Hi All,

i have created an analytical app, below is a description of what is supposed to do:

1. End user will place the input excel file into a shared drive location

2. the app will pick up the excel file 

3. using a list box the user will select only the columns required in the output - the output then becomes the following months input

eg:

Month1 : input 1 contains columns A,B,C D, E,F end user selects B,D,E,F output contains  columns B,D,E,F

Month2  : input will contain columns B,D,E,F in the list box user should have B,D,E,F as options to select from

Issue:- when we move to the month 2, the application picks up column headers configured as per prior months columns and does not refresh to the new ones. user can see A,B,C D, E,F should be able to see B,C D, E,F

in designer i can still use it by running the workflow twice once using ctrl - R and then using the magic wand button, but on the server it does not help as there is only one run option, any help would be great.

to summarize, is there a way for the analytical app to pick up the new column headers from the input when the month changes

[DavidP]

Hi @olimpio 

Here's a trick that @Aguisande taught me. Using a File browse to select the excel file you want to load. The list box only populates with the available fields once a file has been selected and the checked fields are used to update a Select tool.

[olimpio]

Thanks alot, but i think i missed to mention the once the excel file is input, there are some transformations and calculations to the data.

in my workflow after the input and before the select there are some transformations post which the data flows into the list-box.

also, the input is not a file browse, it is being picked up using *.xslx, would this change anything

[DavidP]

Hi @olimpio 

What happens in reality is that the list box entries actually get determined before the app is run. Alteryx tries to figure out from the upstream logic what the list values will be. While you would logically think that something like below would work, in reality it doesn't.

The reason is that Alteryx cannot predetermine what the output of the crosstab tool will be and therefore does not populate the listbox with the desired result. What happens instead is what you probably saw in your own workflow - you have to run the workflow and if you then run it as an app, it populates the listbox from the previous run.

[DavidP]

Hi @olimpio 

Myself and @Aguisande discussed you issue further and he has a solution that might work for you. AJ will post the app here when he gets a chance.

[Aguisande · Accepted solution]

Hi guys!

What we talked about can be solved with a set of 2 chained apps, where we read the actual file in the first one, and present the user the filed list on the second.

For the first app, we built a simple workflow that reads the desired file and saves it's structure to a .yxdb on the disk.

We used some tricks in this one:

- The first is we just read the metadata (Limit number of records in the Input Data tool= -1)

- The second is we created a row for that metadata that repeats the field names as a row of data (I used a macro created byJacob Kahn) .

- The last one is that we created a fixed RecordID, so we can Transpose the data and have our required [Name] and [Value] columns

Those values are then saved in the .yxdb

And this is the Interface Designer config for this app:

And the UI for this App:

Important: Remember to change this workflow Type to Analytic App before saving.

The second App is very simple: 

It points to the same file, and has the ListBox configured to "External Source", pointing to the YXDB generated by the first App.

From here, it's up to you to do some "Alteryx Magic" and process the selected data.

I hope this helped in some way.

[olimpio]

firstly, thank you so much for taking the time to respond.

also, would it be possible to share the Worklfow, i have never created a chained app before, so even though your idea is great im a bit lost.

few questions. unforutnately i cant share my workflow. but could provide some more details.

Questions:

1. are there 2 separate workflows?

2. would this work on the alteryx server?

3. how does the connection between the 2 work?

4. does the 1st output have to been a yxdb file?

[Aguisande · Accepted solution]

Hi @olimpio 

On your questions:

1. In this case yes, there are 2 workflows (could be more if needed)

2. Yes it will, if you set the right permissions and have everything available for the App (macros, file paths, etc)

3. I guess you're calling "connection" to the ability to execute a second workflow once the first ends running. If that's what you mean, it's explained in the previous post

4. It's better to use it, cause it's fast and it's only used to keep the file structure between the two apps.

I always prefer not to share the workflows because the main idea is that everyone tries building them, as a learning process.

But since you asked for, I attached them to this post.

EDIT: I don't know why I can't upload the workflows. I'll check with Community team.

In the meantime, try following the previous post to build it yourself.

[olimpio · Accepted solution]

Hi Aguisande, thanks again for your prompt response.

I did try the steps you mentioned, with only one change. Instead of using the macro and transposing.

we used the field info tool and a formula tool to create the name and value column in the 1st workflow.

current structure of the workflow is mentioned below:

1. Workflow 1 will run and create 2 outputs:

     Output 1: new column headers - to be used in 2nd workflows list-box and dropdown

     Output 2: will be the new input in the 2nd workflow

2. Workflow 2:

     Will pick up the new input from workflow 1 - Output 2

     The list box and drop downs will now contain the new column headers created from workflow 1 - Output 1

     The select columns will then move forward to next steps of the workflow and create the required outputs

** the Drop down were new requirement from the business, but the logic you provided for list box works fine for that as well

David and Aguisande, Thank you so much!! for your assistance and prompt response, I really appreciate it!!

[Aguisande]

Hi @olimpio 

This is exactly what we pursue! We suggest an approach, and you make it even better!

Nice job!

PS: @DavidP suggested the Field Info tool for the names, I just didn't listened 😋