Ttl Values List
Aron Eugine
I have a feature layer that contains sampling results from various sites. Each site can have from one to several hundred records. I'd like to be able to create a List widget where the values are the unique site names, i.e. each site listed just once, not one for each record. When I try setting Site Name to Unique in the builder, I get an error message telling me the SQL is incomplete (Site Name is Unique). I'm looking for the equivalent to "SELECT DISTINCT([Site Name]) FROM Layer". I can do this in Dashboard with Arcade. Is it possible in Experience Builder? Thanks!
Good question and I am looking for a similar answer for either the List or Filter widgets that they could be pre-populated with a unique list of values from an attribute field (in desktop it would be as simple as 'select by attribute' and selecting from a list right? And Dashboards also allow you to do this. I'm also quite new to Experience Builder so may have missed a trick. There is this post which might help on how to populate a list -experience-builder-questions/experience-builder-unique-values-i... but it's rather a workaround through loading in a separate table which could be a maintenance headache. In the Filter tool you can set up a clause set of unique values but you have to set them up individually and if your data changes with say a new site (or new bird species in my case) you have to remember to add in the new categories!
I ended up having to create a separate feature layer of just the sites. Even if I got distinct values from the first layer, it didn't help. The distinct values weren't tied to a particular location, so couldn't do actions like zooming the map.
Your code does work, however the issue is that the list, "Origlist" is a list of lists, not just a single list, which is the way your have the list defined in your script. So the issue is, how to get the list of lists into the data table.
could you please explain how to create a datasource like that from AEB?
I have a table contains team names, I want to create a list of distinct team names, but I cannot find out how to do it. I use the team's name to filter the map.
It is probably my lack of understanding entirely how the tool works, but I just want to populate my listbox with the values in one of the columns from my source table. Is this possible? From my initial understanding, the listbox takes the column "headers", not the rows under a single column. Is that correct? Or is it possible to take the values from a single column, rather than the headers? I've seen some stuff where you can use the Cross Tab tool to essentially pivot the row values into columns and then use those in your list box, but it seems like I would need to create a chained app that performs the Cross Tab pivot, and then that feeds into my listbox... It feels like this is a bit of overkill to just grab all of the values in a column and place them in a listbox, but maybe that is the case. Also, if I use Cross Tab to create a bunch of columns from my rows, will that cause any performance issues if I am "Cross-tabbing" like 100,000 rows?
On a similar line of thought, I basically just want to give my user the ability to select any number of invoices he/she wants to see and then have those generate some reports that I am going to put together for those specified invoices. I figured having the invoices (which are currently in a single column), available in a listbox, the user could select the invoices he/she wants, and that would be the most effective way to get the reports they want. If there is another way to do this, I'm definitely open to suggestions on that front.
It is possible, I completed the same thing a few days ago for the first time. Your below explanation re cross tab to create headers to populate the list is also how I accomplished mine (although admittedly I don't know if there is another way to do this).
You're question regarding if I really want 100,000 list box options is exactly what I have been trying to think through. It seems like a pretty unmanageable amount. It is going to be a list of invoices, and I figured I could sort ascending to make it somewhat easier for the person to find. I was thinking of just using a Text Box interface tool, so that the user could enter in a specific invoice they want to search for and generate a report for that. But I also wanted to allow them to enter multiple invoices, rather than only let them do one at a time, which is why I thought a list box made more sense.
Would you happen to have any suggestions of how I could ultimately achieve what I am trying to do (give user ability to enter multiple invoices), without needing to use a massive list box.... I wonder if it is possible to use a Text Box, but then specify that they use a comma between invoices if entering multiple invoices... then I wonder if there is a way to take that Text Box input, look for commas, and somehow break those into separate parameters?... Does any of that make sense (clearly I'm still pretty new to alteryx).
Not sure about the options here re text box (I am pretty new to Apps as well) but is there a way to limit the amount of invoices options available in the fist place i.e. only provide invoices relating to a specific time periods or users.
I'm working with some data and from looking at a sample it looks like there are a few values where someone has manually inputted "N/A" and similar values. I would usually open data in excel and look at what values are in the column using the filter option, to do the relevant find and replace, but the main data that I'm using is too big for my computer to open in excel.
your question can be taken in one of two ways. @estherb47 has a correct solution to generate a distinct list of rows per field. I would add that using a Join Multiple by row position will generate the list regardless of whether the number of values per field is equivalent. See attached wf1. Again, credit to @estherb47
one downside of doing it in that fashion is the need for a new Summarize tool per incoming field. Check out wf2 for a dynamic way of getting the same list. This is the second workflow. It can handle any amount of incoming fields. Please note that getting these lists in any fashion can be computationally expensive as the data grow in size.
Here is an answer to the second interpretation of your question. I am not sure you're looking for this and it might be largely academic. Still, fun workflow. Between this and the wf2, your question probably has been resolved.
Transpose the data to get everything into 2 columns. I include the sort for my own sanity in viewing, and for the downstream Tile tool.
Unique on both columns will get you all unique header/value columns. The Tile tool assigns row numbers to rebuild the table. And then a crosstab tool to rebuild.
Let me know if this is easier. No summarize required!
Cheers,
@EstherB47 @seven thank you both! Sorry, in my original post it made it seem like I wanted to separate strings and numbers - I just meant these as example columns, I should have done this differently... but both give the result I was looking for! thanks so much
After struggling with this for a couple of days I am giving in and seeking help from the Power BI Gods.... Thanks in advance for any tips or pointers, I truly appreciate it and so does my wife for keeping me sane
The lists in each row have no uniformity. They could contain no records or multiple records. I am trying to extract the "name" from each record in the list and combine them into a new column.
What I have I been stuck on for days now is how to work out how many records are in the list for a row and then loop through all those records to extract the names. The above code also results in the following error when the row has a list with no records in it.
I feel like I am making this far more complicated then it needs to be and the solution is probably stupidly simple but I just can't seem to figure it out. Thanks in advance for your help and advice. Cheers
Right now, the results are giving me an error because "Record" in WorkerName is not a text value. I imagine I need to nest a portion of the code above into another segment, but I have no idea where to go from here.
Can anyone confim if it is possible to use the each function to iterate to through the list of records and store the value in an array which we can then use a text.combine to store it as a string in the new custom column?
@jim you are a true genius! Thank you this has worked perfectly! Much appreciated. Out of interest what is the best practice way to handle this situation? Is extracting the values into a comma seperated list ok or is it recommended to extract them to a seperate table and create a relationship?
Another common practice is to return a list of names in the new column (removing Text.Combine part can achieve this). Then expand the list column to new rows. This will add multiple new rows into the table with other column values copied. This may make it easier when you want to do aggregate calculation per name.
Extracting them to a separate table is also ok. For example, you can remove duplicated values and use the separate table as a Dim Table. Then connect the Dim table to other Fact tables. Create a model like a star schema.
Thanks for taking the time to review my challenge and offer up a solution. Much appreciated. When I tried your suggestion it results in the following error for every row (even ones where the list contains multiple records).
If the formula did work looking at List.Select it is going to return a list object so I believe we would need to put this inside of a text.combine function so that it aggregates all the values together separated by a comma as a text field. But I can do that once I get the actual list working.
FYI that Record.FieldNames returns a list with the field names and 0 gets the first one. The 0[name] at the end gets the first (and only) element of the selected list and the value in the [name] field.
c80f0f1006