Selecting a random row from a table?

[thomas.1 (Thomas Gray)]

I’m building a dashboard that will enable users to randomly select work for auditing, from a filtered list of potential work.

What I have in mind is a button that would randomly select a row from a table.

Would that be done using a script? I there a cleaner way to do it?

[thomas.1 (Thomas Gray)]

I think I’ve got some of the elements of a solution…

1. I added a Count and a SUM(Count), which give me a column containing the total rows in the table.
2. This script is supposed to give me the value of a selected cell in the table:

var members = table1.getSelectedRowHierarchyMembers();
var index = members[0].caption;

So my thought is to do a calculation using the contents of any cell in the “SUM(Count)” row, then do an INT(RAND(SUM(Count)) to get the row number. Then that randomly selected row number can be used to pull the desired value (in this case a URL) from that row.

What’s my next step?

[derek.sell]

Hi Thomas,

You can use the Record Sampling transformation at the cube level with the cube being warehoused and then create a button that the user would click to have the warehouse build and generate another random number/id etc.

https://www.dundas.com/support/learning/documentation/cleanse-consolidate-modify-data/transforms/other/record-sampling

Script would be something like this:

// Get the data cube service
var dataCubeService = this.getService(“DataCubeService”);

// Build the data cube with the specified ID
dataCubeService.buildStorage(“ID of Data Cube”);

setInterval(function(){setTimeout(function(){window.location = window.location; }, 5000);}, 10000);

[thomas.1 (Thomas Gray)]

Would that allow for filtering the table? The selected work needs to be filtered so that it matches several criteria (hasn’t been audited before, the work matches the skill of the auditor, etc.).

[derek.sell]

Good question and I don’t think I can answer that.

My understanding is it would randomly generate whatever field you want (in your case a URL) then the user would need to click the button on the dashboard to rebuild the cube and generate another random URL.

You can always set the filters at the cube level if they are a specific set of filters but if you want users to be able to filter themselves and then get a randomly generated URL I’m not sure my suggestion is what you need.

[thomas.1 (Thomas Gray)]

How about my idea above? I was typing it in at the same time as you were making your suggestion, so you may not have seen it.

If there’s a way to use the “pull data from a specific cell” then I think I can get what I want by randomly selecting a row number.

[derek.sell]

Sorry I don’t have an answer for you.

Hoping some Dundas BI pros step in and get you what you need!

[thomas.1 (Thomas Gray)]

Thanks Derek! Love those Dundas pros.

The script I’d found above didn’t turn out to be useful, it identifies the column that’s selected in a table and what I want is to identify the value in a pre-determined cell in a table.

This discussion looks useful but it’s beyond my abilities. I think it explains how to specify a cell in a table, but it doesn’t describe how to use that information to get the value from that cell.

How to identify the member value from the column caption

Hi Shane, I see what you are trying to do but you can’t look up values like this. All the data you’d want to use is organized into Columns, Rows, Cells and Hierarchies. Here is an example where you can see all of these at play at the same time. Note the table at the top of the screenshot as it’s the data from this table that I am inspecting in each example. [image] This table contains Row Headers, Cells, Columns and Hierarchies to account for different data types. To help you see the API,…

[romero.olivier (Olivier Romero)]

Hi Thomas,

Have you tried something like this:
var theDataResult = Table1.metricSetBindings[0].dataResult;
// check that the dataResult is not null, then
var theCellSet = theDataResult.cellset;
//check that the cellset is not null, then
var theRows = theCellSet.rows;
var theRowIndex = 0;
for (var index = 0; index < theRows.length; index++)
{
// theRows[index] correspond to the row you’re looking at
//theRows[index].members[theColumnnNumber] corresponds to the value of the column you want to
//compare with the predetermined value
//you can find the column number by using “debugger;” and then look into the members of the row
if (theRows[index].members[theColumnNumber] === thePredeterminedValue)
{
//you’ve found the row having the value you were searching for in the column you want to check
theRowIndex = index;
break;
}
}
//the row you were looking for is
//theRows[theRowIndex]

I hope this helps,
Olivier