12721_en-US

liliconcom

JSL- Sum :qty sorted by rows with same string in :city<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Hi everyone,

I'm new to scripting and am at a total loss trying to find informational tutorials on JSL. I will really appreciate any help at all with my problem.

Basically, I need to sort by :City and sum up the value in each item's :Qty and then display it. Problem being that I need to dynamically put together a list of all the different strings in :City and then sum up all numeric values in :Qty for those rows that have matching strings in the :City column.

So for example the table would look like this

tableEx =

City	Qty
a1	100
a2	10
a9	29
a1	99
a4	20
a7	91
a2	60
a100	33
a500	30
a100	14
a99	2000
etc...	etc...

This next part doesn't need to be read to solve my issue, it just details how I wish to solve the problem in my mind. I wish I could do something like this;

List_Of_CityNames = new List();

switch = false;

Foreach Row r in tableEx

{

     Foreach String city in List_Of_CityNames

     {

          if( r.Get(City) == city)

               switch = true;              //means this row's :City entry is already on the list so we don't need to add it.

     }

     if(switch == false)                  //if this row's :City hasn't been put on the list yet then this executes

     {

          List_Of_CityNames.Add( r.Get(City));    //city added to list

     }

     switch = false;                       //switch reset to City not found on list yet

}

If this would work I'd have made the list out of tuples and made it a string + int pair to allow me to sum up the qty of matching cities as well in the above loop. Then I would just print it out by making a New Table() if I could.

After gathering the above information I would then need to print it out, Would anyone happen to know how to create a table based off of the dynamic size of the list which may or may not be a part of the first solution.

dtSolution = New Table("Solution",

Add Row(/* numeric value representing number of different city names */),

New Column("/*first city in the list*/", "Continuous", Set Values( list( /* variable that represents the sum'd :Qty for same Cities*/ ))),

New Column("/*second city in the list*/", "Continuous", Set Values( list( /* variable that represents the sum'd :Qty for same Cities*/ ))),

New Column("/*third city in the list*/", "Continuous", Set Values( list( /* variable that represents the sum'd :Qty for same Cities*/ ))),

New Column("/*fourth city in the list*/", "Continuous", Set Values( list( /* variable that represents the sum'd :Qty for same Cities*/ ))),

New Column("/*fifth city in the list*/", "Continuous", Set Values( list( /* variable that represents the sum'd :Qty for same Cities*/ ))),

New Column("/*sixth city in the list*/", "Continuous", Set Values( list( /* variable that represents the sum'd :Qty for same Cities*/ ))),

etc until every city has been added to the table     //After creating the new table I would probably have to use a foreach loop to add each row one by one to the table based off of the list that was built in pt.1

);

I really appreciate any help that I can get. I also thank you if you did end up reading through all of that.

Thank you,

Loysius

Re: JSL- Sum :qty sorted by rows with same string in :city

Loysius,

I should have mentioned that Tabulate appears in the Tables menu in JMP 10. It was moved to the Analyze menu starting with JMP 11.

-Michael

Re: JSL- Sum :qty sorted by rows with same string in :city

The little red triangle in the tabulate window allows you to save the tabulation as a data table.

Using Michael's solution, after you create the desired tabulation, click on the red triangle and select "Make into data table".  This essentially mimics what I did in JSL.

Re: JSL- Sum :qty sorted by rows with same string in :city

Thank you Michael and PMroz!

I found Tabulate under the Tables menu but thanks a ton for your feedback. I didn't know it was possible to copy the JSL version of tables to the clipboard and paste them into my script.

Sadly they don't copy paste such that they are directly usable, they still need to be modified before they work. In which case PMroz's solution shows the syntax to get everything working properly.

Sincerely,

Loysius

Re: JSL- Sum :qty sorted by rows with same string in :city

Here it is as a JSL program:

// Create an example table

dt = New Table( "CityQty", Add Rows( 11 ),

    New Column( "City", Character, Nominal,

        Set Values( {"a1", "a2", "a9", "a1", "a4", "a7", "a2", "a100", "a500", "a100", "a99" } ) ),

    New Column( "Qty", Numeric, Continuous, Format( "Best", 12 ),

        Set Values( [100, 10, 29, 99, 20, 91, 60, 33, 30, 14, 2000] ) )

);

// Make an invisible tabulation

dtab = dt << Tabulate(

    Show Control Panel( 0 ),

    Add Table(

        Column Table( Analysis Columns( :Qty ) ),

        Row Table( Grouping Columns( :City ) )

    ), invisible,

);

// Convert the tabulation into a table

dt_new = dtab << Make Into Data Table;

// Delete the tabulation

dtab << close window;

Re: JSL- Sum :qty sorted by rows with same string in :city

If you have the City and Qty columns in a data table, you could probably use Tabulate (under the Analyze menu).

Go to Analyze > Tabulate.

Drag City to "Drop zone for rows".

Drag Qty to the header where it now says "N".

Click Done.

I think this should give you a sorted list of cities with their corresponding Qty sums.

To reproduce this later via a script, you can use the red triangle menu in Tabulate to go to script > Save Script to Script Window (or save to various other locations, including the original data table itself).

Hope that helps,
Michael