How to query a large data table based on a column level and return columns whose name contains that level?

markschahl

We want to build a quality monitoring application to deploy across dozens of production facilities (eventually publish to JMP Live).

 

For each facility, we have a very tall & wide data table (let's call it dtBigBoy, <1000 cols, 10k-100k rows) that is refreshed from a process data historian which cannot be queried directly (we get all the data between start/stop timestamps and concatenate the fresh data to the existing table). One of the columns :Grade contains the product name being made at the time (for that row). The column naming follows: Grade.Property.#. There are at least 1 Grade.Property.# column per Grade. One of the user cases is user is interested in a certain grade (say ABCD001) and wants to see all the properties data.

 

1. What is the most efficient way to do this? Use local data filter on dtBigBoy and proceed with control charts, etc.?
   OR select only rows where :Grade == Chosen.Grade and column names contain Chosen.Grade and create a subset?
2. How does one create a selection dialog box which reads the column :Grade and presents the levels in :Grade as choices? The user choice is saved to variable Chosen.Grade.
3. How to select columns whose name contains Chosen.Grade?
4. Is it more efficient to do 1-3 OR use JMP Table Query to get the rows & columns we need from dtBigBoy?

Thanks in advance from wet & steamy Kuala Lumpur, Malaysia!

Craige_Hales

Not sure, but I think the local data filter is going to be similar to a linked subset; the filtered table depends on the table remaining open. 1e3 rows * 1e5 cols * 8 bytes/number is under 1GB and should fit in 16GB+ memory OK. Linked subsets should not significantly increase the memory required.

You can try the summary platform interactively to get a summary table of the grade column. You can get the script from that.

You can script the subset platform in much the same way. You'd write a loop to choose the cols for the subset.

I think it is unlikely anyone knows the answer to the last question without trying both. I think either might be fast enough.