5357_en-US

liliconcom

Accessing unique values of a column in control chart code<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Hi,

Started to work on JMP for the last couple of weeks and have a question on how to get unique values from a column and use those values as inputs in designing a control chart.

1. Step 1: Importing an excel sheet; this step works fine.
2. Step 2: Trying to create a control chart from the excel sheet data. However I need help in the Track Names part where Track Names is a column in the excel sheet which has duplicate values. What I want to do is first create a unique set of values from that column and then use those unique set of values in the for loop. I have highlighted the part in yellow where I am stuck..
3. My thoughts: Initially what I did is load the unique track names ( done manually in excel) as a list and then tried to access the contents of the list in the for loop by using the show command. However that doesn't work. Not sure the exact reason but I think its mainly because I'm accessing the week and calculation column from the excel sheet but the Track name from the list so I guess JMP doesn't like that.

Please let me know how to go forward with this. Thank you for your help in advance.

//Selecting the JMP Table sheet from the Consolidation Macro excel file.

Open Database("DSN=Excel Files;DBQ=C:\Users\XXX.xlsm;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;",

"SELECT * FROM `abc$`","abc Data table$");

A = {"John","Pace","Rodriguez"};     // Created a unique list from the Track Names column manually.

New Window( "Control Charts for NA & LA Client",

V List Box(

for(i=1,i<=NItems(A),i++,

Control Chart(

Sample Label( :WEEK ),

//Alarm Script( Write( "i,", qc_test, ",", qc_col, qc_sample, "\!N" ) ),

Group Size( 1 ),

Ksigma( 3 ),

Chart Col( :Calculation, Individual Measurement( Show Zones( 2 ), Shade Zones( 2 ), Test 6( 1 ) ) ),

Where( :Track Names == show(A) ),

),

), 

)

)        ;

Re: Accessing unique values of a column in control chart code

That's a function that will allow you to dynamically get the unique values in a column.  Put the function in as the first part of your code, and then call it like this:

alist = get unique values(:Track Names);

I'm not sure how you would use it for the control chart section of your code though.

Re: Accessing unique values of a column in control chart code

Hi,

Thanks for your prompt reply...Not sure I understood your answer...I'm trying to use this but not getting it..

Get Unique Values = Function( {dtcol},{default local},

// Eval/expr trickery to get this to work.

    eval(eval expr(

        Summarize(unique_list = By(expr(: Track Names)))));

    unique_list;

); 

Re: Accessing unique values of a column in control chart code

This function will give you the unique values in a column:

//------------------------------------------------------------------------------

/* Function Name    Get Unique Values

Description:    Get the unique values for a column.  For example:

    Indications

    ------------

    AAAAA

    BBBBB

    AAAAA

    CCCCC

    CCCCC

unique_list = get unique values (:Indications)

will return {"AAAAA", "BBBBB", "CCCCC"}

Arguments:

dtcol        column descriptor to calculate unique values for

*/

Get Unique Values = Function( {dtcol},{default local},

// Eval/expr trickery to get this to work.

    eval(eval expr(

        Summarize(unique_list = By(expr(dtcol)))));

    unique_list;

);    // end get unique values