Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- JSL- Sum :qty sorted by rows with same string in :...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 8, 2015 7:57 PM
(1077 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Solution

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**;

5 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 9, 2015 6:18 AM
(699 views)

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

Michael Crotty

Sr Statistical Writer

JMP Development

Sr Statistical Writer

JMP Development

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 10, 2015 4:22 PM
(699 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 11, 2015 5:45 AM
(699 views)

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 11, 2015 10:37 AM
(699 views)

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

Michael Crotty

Sr Statistical Writer

JMP Development

Sr Statistical Writer

JMP Development