Subscribe Bookmark RSS Feed

Problem of extracting data from excel

jade

Contributor

Joined:

Apr 22, 2017

Hi, everyone

Is it JSL have possible to extract the data from excel file which can make like S_GX_F40(Family_D) have these two parameters list(G700 and S800-see second attachment[i use the excel to make a small summarize] )??What the problem that i meet is when i run the script to extract the data from the excel file, it can extract out the names and parameter list out, BUT it extract all the rows of the excels file.

But then what i now want is like photo 2: One Family_D generate 2 parameter list(meant now i have 5 Family_D then each of Family_D has 2 parameter list). So, in my final output, I only want to extract 5 Family_D and 2 parameter list in order to generate the chart.(That meant i only have 5 Family_D x 2 parameter list But NOT 10 Family_D(every row of excel) x 2 parameter list).Therefore, my final is only want 5 interface(5 Family_D) but inside have 2 parameter graph each.
So, can anyone help me solve this out?
thanks.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
ih

Contributor

Joined:

Sep 30, 2016

Solution

This seems like homework...

 

If I understand you correctly you want to re-create the pivot table in JMP.  After importing the table you might start with the Tabulate platform and do something like this:

 

dt = New Table( "Untitled",
	Add Rows( 4 ),
	New Column( "a", Character, "Nominal", Set Values( {"a1", "a1", "a2", "a2"} ) ),
	New Column( "b", Character, "Nominal", Set Values( {"b1", "b2", "b1", "b2"} ) )
);

dt << Tabulate( Add Table( Row Table( Grouping Columns( :a, :b ) ) ) );

 Giving output similar to this:

Capture.PNG

ian_jmp

Staff

Joined:

Jun 23, 2011

Solution

Building on the previous reply:

 

// Make an example table
dt = New Table( "Untitled",
	Add Rows( 4 ),
	New Column( "a", Character, "Nominal", Set Values( {"a1", "a1", "a2", "a2"} ) ),
	New Column( "b", Character, "Nominal", Set Values( {"b1", "b2", "b1", "b2"} ) )
);

// Make a list of columns, 'gCols', to be used for grouping (in this case, it's all the columns in the table)
gCols = dt << getColumnNames("String");
// Do the tabulation using the list
dt << Tabulate( ShowControlPanel(0), Add Table( Row Table( Grouping Columns( Eval(gCols) ) ) ) );
4 REPLIES
ih

Contributor

Joined:

Sep 30, 2016

Solution

This seems like homework...

 

If I understand you correctly you want to re-create the pivot table in JMP.  After importing the table you might start with the Tabulate platform and do something like this:

 

dt = New Table( "Untitled",
	Add Rows( 4 ),
	New Column( "a", Character, "Nominal", Set Values( {"a1", "a1", "a2", "a2"} ) ),
	New Column( "b", Character, "Nominal", Set Values( {"b1", "b2", "b1", "b2"} ) )
);

dt << Tabulate( Add Table( Row Table( Grouping Columns( :a, :b ) ) ) );

 Giving output similar to this:

Capture.PNG

jade

Contributor

Joined:

Apr 22, 2017

Thanks ih for replying.
May I know is it possible to do the pivot table without hard coding? Because I have a lot of data, it is impossible for me to list down what is the value of a, b, c, d, e etc..
So, is there any alternatives way can do it?
Thanks.
ian_jmp

Staff

Joined:

Jun 23, 2011

Solution

Building on the previous reply:

 

// Make an example table
dt = New Table( "Untitled",
	Add Rows( 4 ),
	New Column( "a", Character, "Nominal", Set Values( {"a1", "a1", "a2", "a2"} ) ),
	New Column( "b", Character, "Nominal", Set Values( {"b1", "b2", "b1", "b2"} ) )
);

// Make a list of columns, 'gCols', to be used for grouping (in this case, it's all the columns in the table)
gCols = dt << getColumnNames("String");
// Do the tabulation using the list
dt << Tabulate( ShowControlPanel(0), Add Table( Row Table( Grouping Columns( Eval(gCols) ) ) ) );
ih

Contributor

Joined:

Sep 30, 2016

The tabulate platform is under Analyze menu, when you open the actual platform you can create the table without any code at all. Is that what you are looking for?

tabulate menu.PNG

tabulate.PNG