11839_en-US

liliconcom

Joining two Data Sets<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

JMP User Community - Thank you for your assistance in answering my JMP questions.  I have another question.

I am attempting to join two data tables by the field "Charge No"  the two data tables originated from SAP reports.  The challenge is that:

* In Data Set A the "Charge No" is:                      six numbers + 1 SPACE + four numbers

For example:  141922 0010

*  However in Data Set B the "Charge No" is:     six numbers + 7 SPACES + four numbers

For example 141922       0010

What I would like to do and my question is how to write JMP script that will Modify Data Set B so that the configuration of the "Charge No" is the same as in Data Set A i.e.  six numbers + 1 SPACE + four numbers

Re: Joining two Data Sets

Thanks for this information!

Dan F.

Re: Joining two Data Sets

Hi, Dan.

Regarding your spacing issue...  The Collapse Whitespace() function became available in JMP 11.  The following will eliminate duplicate spaces within the values located in the Charge No column.

dtB = Data Table( "Data Set B" );

For Each Row(

  Column( dtb, "Charge No" )[Row()] = Collapse Whitespace( Column( dtb, "Charge No" )[Row()] )

);

Hope that helps!

Wendy

Re: Joining two Data Sets

Thanks – That worked perfectly!

I’ve run into an issue that I don’t understand. I’ve used JMP code similar to that below to send a *.JPEG picture to a designated folder location in my computer (File Location\Charts). I’ve used code similar to this for over 5 years without issue and suddenly around 04/09/2015 the code no longer works? I’m not getting any error messages in the log but the picture is not being copied to the desired file location?

As I said I’ve used identical JMP code for years without issue and now it is not working. I’m wondering if this is somehow related to the recent renewal at about the same time this code stopped working of my JMP license.

See a copy and paste from the JMP Log below:

PathforMain = "C:\Users\fuscod\Desktop\Common\SAP Reports\15_04_08 LV1 SD MODs\";

PathforCharts = "C:\Users\fuscod\Desktop\Common\SAP Reports\15_04_08 LV1 SD MODs\Charts\"; (This is where I want to copy the Tabulate picture to)

CT = open(PathforMain || "LV1 SD MODS Planned vs Actuals-ES.jmp");

T1 = CT << Tabulate(

Show Control Panel( 0 ),

Add Table(

Column Table( Analysis Columns( :Planned Costs, :Actual Costs, :Delta Costs ) ),

Row Table( Grouping Columns( :Charge No, :Description ) ),

Row Table( Statistics( Sum ) )

)

);

T1 << Title("LV1 SD MODS ES Planned vs Actuals Costs");

/// Make HTML File for Dashboard

// Make Journal as Intermediary for Table

MyCJnl = New Window("DT",

<< Journal

);

T1 << Journal(freeze pictures);

MyCJnl << Save Picture(PathforCharts || "LV1 SD MODS ES Planned vs Actuals Costs.jpeg", JPEG);

MyCJnl << CloseWindow;

close (CT, nosave);

Re: Joining two Data Sets

I corrected my syntax.  Should be

dtb:Charge No = substitute(dtb:Charge No, "       ", " ");

Re: Joining two Data Sets

Thank you – When I run this:

dtb = data table("Sheet1");

for (i = 1, i <= nrows(dtb), i++,

dtb:Charge No = substitute(a, " ", " ");

);

I GET THIS ERROR:

Name Unresolved: a in access or evaluation of 'a' , a/###/

Re: Joining two Data Sets

Thank you!

Dan F.

Re: Joining two Data Sets

dtb = data table("Data Set B");

for (i = 1, i <= nrows(dtb), i++,

     dtb:Charge No[i] = substitute(dtb:Charge No[i], "       ", " ");

);