Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
debraj_guha
Level II

How do I create a singe script to get data from database and then perform my analysis with that dataset

Hi all,

 

I am trying to create a JSL script that pulls data from a database, then creates various plots and performs some analysis with that dataset. Till now, I have been able to use the Database query builder to get the data I need but cannot use the same script to do the plots I need. Being new to scripting in JSL, this seems to be a really daunting task. Below is the script with which I have been able to get data from 2 databases and merge them together. However, I want to save this data table and then create plots. If I try to add an Obj reference before the query, it does not return any data. Any suggestions?

 

Best,

Debraj 

 

 

New SQL Query(

    Version( 130 ), 

    Connection(
        "ODBC:DSN=Parametric 32;Description=Parametric 32;UID=GUEST;PWD=;APP=JMP;WSID=DEBRAJG-W10LT;"
    ), 

    QueryName( "LSO-THK" ), 

    Select(

        Column( "WAFER", "t1" ), 

        Column( "DATE_TIME", "t1", Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) ), 

        Column( "LOT", "t1" ), 

        Column( "TESTER", "t1" ), 

        Column( "RECEIPE", "t1" ), 

        Column( "THICKNESS_1", "t1" ), 

        Column( "THICKNESS_2", "t1" ), 

        Column( "THICKNESS_3", "t1" ), 

        Column( "THICKNESS_4", "t1" ), 

        Column( "THICKNESS_5", "t1" ), 

        Column( "THICKNESS_6", "t1" ), 

        Column( "THICKNESS_7", "t1" ), 

        Column( "THICKNESS_8", "t1" ), 

        Column( "THICKNESS_9", "t1" ), 

        Column( "THICKNESS_AVG", "t1" ), 

        Column( "THICKNESS_STD", "t1" ), 

        Column( "THICKNESS_MIN", "t1" ), 

        Column( "THICKNESS_MAX", "t1" ), 

        Column( "THICKNESS_RANGE", "t1" ), 

        Column( "OPERATION", "t2" ), 

        Column( "WIP_MSG_KEY", "t2" ), 

        Column( "WIP_MSG_LEVEL3", "t2" ), 

        Column( "WIP_MSG_LEVEL2", "t2" ), 

        Column( "WIP_MSG_LEVEL1", "t2" )

    ), 

    From(

        Table( "WFLSOTHK", Schema( "dbo" ), Alias( "t1" ) ), 

        Table(

            "FT_WF_HIST", 

            Alias( "t3" ), 

            Join(

                Type( Left Outer ), 

                EQ(

                    Column(

                        "DATE_TIME", 

                        "t1", 

                        Numeric Format( "m/d/y h:m:s", "0", "NO", "" )

                    ), 
                    Column(

                        "DATE_TIME", 

                        "t3", 

                        Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
                    )

                ) & EQ( Column( "LOT", "t1" ), Column( "LOT", "t3" ) )

            )

        ), 

        Table(

            "WF_MESA_WIP_MSG_HIST", 

            Alias( "t2" ), 

            Join(
                Type( Left Outer ), 
                EQ( Column( "WAFER", "t1" ), Column( "WAFER", "t2" ) ) & 
                EQ( Column( "LOT", "t1" ), Column( "LOT", "t2" ) )

            )
        )
    ), 
    Where(
        In List(
            Column( "OPERATION", "t2" ), 
            {"O0TS"}, 
            UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
        )
    )
) >> Run

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
txnelson
Super User

Re: How do I create a singe script to get data from database and then perform my analysis with that dataset

I don't have a database to test this with, but I think this methodology should work.

Names default to here(1);

New SQL Query(
	Version( 130 ), 

	Connection(
		"ODBC:DSN=Parametric 32;Description=Parametric 32;UID=GUEST;PWD=;APP=JMP;WSID=DEBRAJG-W10LT;"
	), 
	QueryName( "LSO-THK" ), 
	Select(
		Column( "WAFER", "t1" ), 
		Column( "DATE_TIME", "t1", Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) ), 
		Column( "LOT", "t1" ), 
		Column( "TESTER", "t1" ), 
		Column( "RECEIPE", "t1" ), 
		Column( "THICKNESS_1", "t1" ), 
		Column( "THICKNESS_2", "t1" ), 
		Column( "THICKNESS_3", "t1" ), 
		Column( "THICKNESS_4", "t1" ), 
		Column( "THICKNESS_5", "t1" ), 
		Column( "THICKNESS_6", "t1" ), 
		Column( "THICKNESS_7", "t1" ), 
		Column( "THICKNESS_8", "t1" ), 
		Column( "THICKNESS_9", "t1" ), 
		Column( "THICKNESS_AVG", "t1" ), 
		Column( "THICKNESS_STD", "t1" ), 
		Column( "THICKNESS_MIN", "t1" ), 
		Column( "THICKNESS_MAX", "t1" ), 
		Column( "THICKNESS_RANGE", "t1" ), 
		Column( "OPERATION", "t2" ), 
		Column( "WIP_MSG_KEY", "t2" ), 
		Column( "WIP_MSG_LEVEL3", "t2" ), 
		Column( "WIP_MSG_LEVEL2", "t2" ), 
		Column( "WIP_MSG_LEVEL1", "t2" )
	), 

	From(
		Table( "WFLSOTHK", Schema( "dbo" ), Alias( "t1" ) ), 
		Table(
			"FT_WF_HIST", 
			Alias( "t3" ), 
			Join(
				Type( Left Outer ), 
				EQ(
					Column(
						"DATE_TIME", 
						"t1", 
						Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
					), 
					Column(
						"DATE_TIME", 
						"t3", 
						Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
					)
				) & EQ( Column( "LOT", "t1" ), Column( "LOT", "t3" ) )
			)
		), 
		Table(
			"WF_MESA_WIP_MSG_HIST", 
			Alias( "t2" ), 
			Join(
				Type( Left Outer ), 
				EQ( Column( "WAFER", "t1" ), Column( "WAFER", "t2" ) ) & 
				EQ( Column( "LOT", "t1" ), Column( "LOT", "t2" ) )
			)
		)
	), 
	Where(
		In List(
			Column( "OPERATION", "t2" ), 
			{"O0TS"}, 
			UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
		)
	)
) >> Run;

dt = data table("WF_MESA_WIP_MSG_HIST");
dt << save("Path to save location");

dt << Graph Builder(.............);
Jim

View solution in original post

Highlighted
debraj_guha
Level II

Re: How do I create a singe script to get data from database and then perform my analysis with that dataset

It turns out that the issue was primarily due to namespaces. I did some digging around and found the solution in the line below.
https://community.jmp.com/t5/Discussions/Getting-a-data-table-reference-from-NewSQLQuery-lt-lt-Run/t...

View solution in original post

4 REPLIES 4
Highlighted
txnelson
Super User

Re: How do I create a singe script to get data from database and then perform my analysis with that dataset

I don't have a database to test this with, but I think this methodology should work.

Names default to here(1);

New SQL Query(
	Version( 130 ), 

	Connection(
		"ODBC:DSN=Parametric 32;Description=Parametric 32;UID=GUEST;PWD=;APP=JMP;WSID=DEBRAJG-W10LT;"
	), 
	QueryName( "LSO-THK" ), 
	Select(
		Column( "WAFER", "t1" ), 
		Column( "DATE_TIME", "t1", Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) ), 
		Column( "LOT", "t1" ), 
		Column( "TESTER", "t1" ), 
		Column( "RECEIPE", "t1" ), 
		Column( "THICKNESS_1", "t1" ), 
		Column( "THICKNESS_2", "t1" ), 
		Column( "THICKNESS_3", "t1" ), 
		Column( "THICKNESS_4", "t1" ), 
		Column( "THICKNESS_5", "t1" ), 
		Column( "THICKNESS_6", "t1" ), 
		Column( "THICKNESS_7", "t1" ), 
		Column( "THICKNESS_8", "t1" ), 
		Column( "THICKNESS_9", "t1" ), 
		Column( "THICKNESS_AVG", "t1" ), 
		Column( "THICKNESS_STD", "t1" ), 
		Column( "THICKNESS_MIN", "t1" ), 
		Column( "THICKNESS_MAX", "t1" ), 
		Column( "THICKNESS_RANGE", "t1" ), 
		Column( "OPERATION", "t2" ), 
		Column( "WIP_MSG_KEY", "t2" ), 
		Column( "WIP_MSG_LEVEL3", "t2" ), 
		Column( "WIP_MSG_LEVEL2", "t2" ), 
		Column( "WIP_MSG_LEVEL1", "t2" )
	), 

	From(
		Table( "WFLSOTHK", Schema( "dbo" ), Alias( "t1" ) ), 
		Table(
			"FT_WF_HIST", 
			Alias( "t3" ), 
			Join(
				Type( Left Outer ), 
				EQ(
					Column(
						"DATE_TIME", 
						"t1", 
						Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
					), 
					Column(
						"DATE_TIME", 
						"t3", 
						Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
					)
				) & EQ( Column( "LOT", "t1" ), Column( "LOT", "t3" ) )
			)
		), 
		Table(
			"WF_MESA_WIP_MSG_HIST", 
			Alias( "t2" ), 
			Join(
				Type( Left Outer ), 
				EQ( Column( "WAFER", "t1" ), Column( "WAFER", "t2" ) ) & 
				EQ( Column( "LOT", "t1" ), Column( "LOT", "t2" ) )
			)
		)
	), 
	Where(
		In List(
			Column( "OPERATION", "t2" ), 
			{"O0TS"}, 
			UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
		)
	)
) >> Run;

dt = data table("WF_MESA_WIP_MSG_HIST");
dt << save("Path to save location");

dt << Graph Builder(.............);
Jim

View solution in original post

Highlighted
debraj_guha
Level II

Re: How do I create a singe script to get data from database and then perform my analysis with that dataset

Thanks a lot Jim. It did work. I believe I was missing the semi-colon after >>run initially which is why it didn't behave the way I was expecting.

However, the script did save and them produced the plots I put in it the first time. Then when I delete the .csv file and re-run, it did not save the file again and plot the curves. It is behaving intermittently. It saves the file and then plots the curves a few times, while in most of the other cases, it just stops after pulling the data from the database. Is there a trick to this that I am missing or a parameter that needs to be changed? Below is the part of the code that I added to the SQL database query and is working intermittently.

 

 

dt = data table("WFLSOTHK");
dt << Save ("C:\path to directory\file_name.csv");
dt << Open ( "C:\path to directory\file_name.csv" );
dt << Fit Group(

Bivariate(
           .
           .
           .
  ),

Bivariate(
           .
           .
           .
  ),

 <<{Arrange in Rows( 1 )}

)

 

Highlighted
debraj_guha
Level II

Re: How do I create a singe script to get data from database and then perform my analysis with that dataset

I switched Save () to Include () and it works fine now. I am not sure why though...….
Highlighted
debraj_guha
Level II

Re: How do I create a singe script to get data from database and then perform my analysis with that dataset

It turns out that the issue was primarily due to namespaces. I did some digging around and found the solution in the line below.
https://community.jmp.com/t5/Discussions/Getting-a-data-table-reference-from-NewSQLQuery-lt-lt-Run/t...

View solution in original post