/*Copyright (C) 2014 (Catherine Hosage Norman, Module 3 Solutions, LLC) Portions based on 2_Extra_WriteTablelAsXML.js - JSL Companion, Utlaut, Morgan and Anderson This code is distributed WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.*/ Clear Globals(); dt = Data Table( "DrugData" ); Current Data Table( dt ); //---- Function tagwrite tagwrite = Function( {colstr, colval}, {Default Local}, newstr = "<" || Trim( colstr ) || ">" || Char( colval ) || ""; newstr; ); //---- Function tagitem tagitem = Function( {mystr, tagtype = "S", attr = "", val = ""}, {Default Local}, If( Uppercase( tagtype ) == "S" & Is Missing( attr ), newstr = "<" || Trim( mystr ) || ">", Uppercase( tagtype ) == "S" & !Is Missing( attr ), newstr = "<" || Trim( mystr ) || " " || Char( attr ) || "\[="]\" || Char( val ) || "\[">]\", Uppercase( tagtype ) == "E", newstr = "", newstr = "" ); newstr; ); ret = "\!r"; // carriage return // gen summary table for lookup d_sum = dt << Summary( Group( :LOT_NUM, :STRENGTH, :PACKTYPE, :STORCOND, :STORTEMP, :STOR_RH, :MANUFACT, :DOM, :LOT_SZ, :STDYSTRT, :PACKLOT ), N, Output Table Name( "LotLookUp" ) ); // transpose result data by timepoint dtrans = dt << Transpose( columns( :RESULT ), By( :LOT_NUM, :STORTEMP, :STOR_RH, :METHOD, :TESTNAME, :CRITERIA ), Label( :TIME_PT, ), Output Table( "DraftDataTrans" ) ); dtrans << delete columns( "Label" ); dtrans << Reorder By Name; dtrans << Move Selected Columns( {"METHOD"}, To First ); dtrans << Move Selected Columns( {"TESTNAME"}, After( "METHOD" ) ); dtrans << Move Selected Columns( {"CRITERIA"}, After( "TESTNAME" ) ); dtrans << Move Selected Columns( {"12"}, After( "9" ) ); dtrans << Move Selected Columns( {"18"}, After( "12" ) ); Column( 3 ) << set name( "ACCEPTANCE CRITERIA" ); lots = :LOT_NUM << get values; lot set = Associative Array( lots ); uniqueLots = lot set << Get Keys; /* start xml string*/ Clear Globals( vv ); vv = "\[]\" || ret; str = "\[]\" || ret; vv ||= str; /*loop through lots and make individual tables*/ nlots = N Items( uniqueLots ); For( ii = 1, ii <= nlots, ii = ii + 1, dtrans << Select Where( :LOT_NUM == uniqueLots[ii] & :STOR_RH == 60 & :STORTEMP == 25 ); selRows = N Row( dtrans << Get Selected Rows ); If( selRows > 0, dtsub = dtrans << subset( output Table Name( uniqueLots[ii] || " 25-60" ) ); dtsub << delete columns( "LOT_NUM", "STOR_RH", "STORTEMP" ); /*rename columns*/ cc = dtsub << Get Column Names; xcol = N Items( cc ); For( i = 1, i <= xcol, i++, x = Char( Column Name( i ) ); If( Munger( x, 1, "E" ) == 0, Column( i ) << set name( Concat( "month ", x ) ) ); ); //dtsub << layout; // get study attributes from d_sum Current Data Table( d_sum ); d_sum << Clear Select; d_sum << Select Where( :LOT_NUM == uniqueLots[ii] & :STOR_RH == 60 & :STORTEMP == 25 ); fetch = d_sum << Get Selected Rows; sum_current = fetch[1]; vStrength = Column( "STRENGTH" )[sum_current]; vpack = Column( "PACKTYPE" )[sum_current]; vmanuf = Column( "MANUFACT" )[sum_current]; vdom = Column( "DOM" )[sum_current]; vlotsz = Column( "LOT_SZ" )[sum_current]; vstart = Column( "STDYSTRT" )[sum_current]; vpacklot = Column( "PACKLOT" )[sum_current]; vstorage = Column( "STORCOND" )[sum_current]; // turn into xml tname = dtsub << Get Name(); str = "\[]\" || ret; vv ||= str; Current Data Table( dtsub ); //-- Create a list of column names colstrList = dtsub << Get Column Names( String ); //--Replace spaces with underscores--Tags need to be simple names For( i = 1, i <= N Items( colstrList ), i++, tmp = Words( colstrList[i] ); colstrList[i] = Concat Items( tmp, "_" ); ); ix = 0; //-- Create a tag for each row in the table For( ix = 1, ix <= N Row( dtsub ), ix++, tmpstr = tagitem( "analysis", "S", "n", ix ) || ret; vv ||= tmpstr; For( jx = 1, jx <= N Items( colstrList ), jx++, str = tagwrite( XML Encode( colstrList[jx] ), XML Encode( Trim( Char( Column( dtsub, jx )[ix] ) ) ) ) || ret; vv ||= str; ); tmpstr = tagitem( "analysis", "E" ) || ret; vv ||= (tmpstr); ); // end ix str = ""; vv ||= str; , Print( "No rows selected" ) ); dtrans << Select Where( :LOT_NUM == uniqueLots[ii] & :STOR_RH == 65 & :STORTEMP == 30 ); selRows = N Row( dtrans << Get Selected Rows ); If( selRows > 0, dtsub = dtrans << subset( output Table Name( uniqueLots[ii] || " 30-65" ) ); dtsub << delete columns( "LOT_NUM", "STOR_RH", "STORTEMP" ); /*rename columns*/ cc = dtsub << Get Column Names; xcol = N Items( cc ); For( i = 1, i <= xcol, i++, x = Char( Column Name( i ) ); If( Munger( x, 1, "E" ) == 0, Column( i ) << set name( Concat( "month ", x ) ) ); ); //dtsub << layout; // get study attributes from d_sum Current Data Table( d_sum ); d_sum << Clear Select; d_sum << Select Where( :LOT_NUM == uniqueLots[ii] & :STOR_RH == 65 & :STORTEMP == 30 ); fetch = d_sum << Get Selected Rows; sum_current = fetch[1]; vStrength = Column( "STRENGTH" )[sum_current]; vpack = Column( "PACKTYPE" )[sum_current]; vmanuf = Column( "MANUFACT" )[sum_current]; vdom = Column( "DOM" )[sum_current]; vlotsz = Column( "LOT_SZ" )[sum_current]; vstart = Column( "STDYSTRT" )[sum_current]; vpacklot = Column( "PACKLOT" )[sum_current]; vstorage = Column( "STORCOND" )[sum_current]; // turn into xml tname = dtsub << Get Name(); str = "\[]\" || ret; vv ||= str; Current Data Table( dtsub ); //-- Create a list of column names colstrList = dtsub << Get Column Names( String ); //--Replace spaces with underscores--Tags need to be simple names For( i = 1, i <= N Items( colstrList ), i++, tmp = Words( colstrList[i] ); colstrList[i] = Concat Items( tmp, "_" ); ); ix = 0; //-- Create a tag for each row in the table For( ix = 1, ix <= N Row( dtsub ), ix++, tmpstr = tagitem( "analysis", "S", "n", ix ) || ret; vv ||= tmpstr; For( jx = 1, jx <= N Items( colstrList ), jx++, str = tagwrite( XML Encode( colstrList[jx] ), XML Encode( Trim( Char( Column( dtsub, jx )[ix] ) ) ) ) || ret; vv ||= str; ); tmpstr = tagitem( "analysis", "E" ) || ret; vv ||= (tmpstr); ); // end ix str = ""; vv ||= str; , Print( "No rows selected" ) ); dtrans << Select Where( :LOT_NUM == uniqueLots[ii] & :STOR_RH == 75 & :STORTEMP == 40 ); selRows = N Row( dtrans << Get Selected Rows ); If( selRows > 0, dtsub = dtrans << subset( output Table Name( uniqueLots[ii] || " 40-75" ) ); dtsub << delete columns( "LOT_NUM", "STOR_RH", "STORTEMP", "9", "12", "18" ); cc = dtsub << Get Column Names; xcol = N Items( cc ); For( i = 1, i <= xcol, i++, x = Char( Column Name( i ) ); If( Munger( x, 1, "E" ) == 0, Column( i ) << set name( Concat( "month ", x ) ) ); ); //dtsub << layout; // get study attributes from d_sum Current Data Table( d_sum ); d_sum << Clear Select; d_sum << Select Where( :LOT_NUM == uniqueLots[ii] & :STOR_RH == 75 & :STORTEMP == 40 ); fetch = d_sum << Get Selected Rows; sum_current = fetch[1]; vStrength = Column( "STRENGTH" )[sum_current]; vpack = Column( "PACKTYPE" )[sum_current]; vmanuf = Column( "MANUFACT" )[sum_current]; vdom = Column( "DOM" )[sum_current]; vlotsz = Column( "LOT_SZ" )[sum_current]; vstart = Column( "STDYSTRT" )[sum_current]; vpacklot = Column( "PACKLOT" )[sum_current]; vstorage = Column( "STORCOND" )[sum_current]; // turn into xml tname = dtsub << Get Name(); str = "\[]\" || ret; vv ||= str; Current Data Table( dtsub ); //--Replace spaces with underscores--Tags need to be simple names For( i = 1, i <= N Items( colstrList ), i++, tmp = Words( colstrList[i] ); colstrList[i] = Concat Items( tmp, "_" ); ); iz = 0; //-- Create a tag for each row in the table For( iz = 1, iz <= N Row( dtsub ), iz++, tmpstr = tagitem( "analysis", "S", "n", iz ) || ret; vv ||= tmpstr; For( jz = 1, jz <= N Items( colstrList ), jz++, If( (colstrList[jz] != "month_9") & (colstrList[jz] != "month_12") & (colstrList[jz] != "month_18"), str = tagwrite( XML Encode( colstrList[jz] ), XML Encode( Trim( Char( Column( dtsub, jz )[iz] ) ) ) ) || ret; vv ||= str; ) ); tmpstr = tagitem( "analysis", "E" ) || ret; vv ||= tmpstr; ); // end iz str = ""; vv ||= str; , Print( "No rows selected" ) ); ); str = ""; vv ||= str; xml_txt = vv; tfname = dt << Get Name(); Save Text File( "C:\JMP\jmp2014Discovery\Example_files\" || tfname || ".xml", xml_txt ); //Display the file that was written New Window( "New XML", Text Box( xml_txt ) ); Wait( 1 ); Web( "C:\JMP\JMP2014Discovery\Example\genworddoc.bat" ); Wait( 20 );