cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
theo_beluzzi
Level I

Joining tables with JSL

I'm a begginer at using JSL, and tried to join two data tables using JSL. However, the code isn't working: it returns a table with empty columns

Can anyone help me?

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: Joining tables with JSL

Hard to tell why it's not working without some data to test things, but I made a few corrections/additions to your code.

 

1. You used the same table variable (dt) for both tables.  Changed this to dt1 and dt2.

 

2. Added table variables for creating the new formula columns

 

3. Use table variables dt1 and dt2 in the join command.

 

3. Added "output table" to the join command, then point dt3 to the joined output. 

 

4. Use dt3 table variable for adding the new column.

 

 

// open and retain a reference
 
dt1 = Open("P:\CompComercialDPK\Inteligencia de Mercado DPK\Theodora\Projetos\Áreas de Influência\Estudos Preliminares\Raio 50 KM - DF.xlsx");
 
// criando a coluna de cidades, concatenando cidade e estado
dt1 << New Column( "Cidades", Character, Nominal, Formula( :Nome || ", " || :Estado ), Set Selected    );
// Abre a base de dados de vendas de 2015 
dt2 = Open("P:\CompComercialDPK\Inteligencia de Mercado DPK\Theodora\Projetos\Áreas de Influência\Vendas 2015.xlsx");
 
// criando a coluna de cidades, concatenando cidade e estado
      
dt2 << New Column( "Cidades", Character, Nominal, Formula( :Desc. Município Cadastro || ", " || :Cód. U.F. Cadastro ),
            Set Selected );
 
//TA DANDO ERRO AQUI MINA
// Join para obter vendas e clientes das cidades
      
(dt1 << Join(
            With( dt2 ),
            By Matching Columns( :Cidades = :Cidades ),
            Drop multiples( 0, 0 ),
            Name( "Include non-matches" )(1, 0),
            Preserve main table order( 1 ),
            output table("Joined Table")
      )
);
 
dt3 = data table("Joined Table");
 
dt3 <<      New Column( "MKT Share", Numeric, Continuous,
            Formula(:Name("Sum(Vlr. Venda Comercial)") / (:Name("Mean(Pot. Consumo Estimado por Categoria (2015) - Manutenção e acessórios/)") * 0.127)),
            Set Selected
      );

 

     

View solution in original post

5 REPLIES 5
pmroz
Super User

Re: Joining tables with JSL

Hard to tell why it's not working without some data to test things, but I made a few corrections/additions to your code.

 

1. You used the same table variable (dt) for both tables.  Changed this to dt1 and dt2.

 

2. Added table variables for creating the new formula columns

 

3. Use table variables dt1 and dt2 in the join command.

 

3. Added "output table" to the join command, then point dt3 to the joined output. 

 

4. Use dt3 table variable for adding the new column.

 

 

// open and retain a reference
 
dt1 = Open("P:\CompComercialDPK\Inteligencia de Mercado DPK\Theodora\Projetos\Áreas de Influência\Estudos Preliminares\Raio 50 KM - DF.xlsx");
 
// criando a coluna de cidades, concatenando cidade e estado
dt1 << New Column( "Cidades", Character, Nominal, Formula( :Nome || ", " || :Estado ), Set Selected    );
// Abre a base de dados de vendas de 2015 
dt2 = Open("P:\CompComercialDPK\Inteligencia de Mercado DPK\Theodora\Projetos\Áreas de Influência\Vendas 2015.xlsx");
 
// criando a coluna de cidades, concatenando cidade e estado
      
dt2 << New Column( "Cidades", Character, Nominal, Formula( :Desc. Município Cadastro || ", " || :Cód. U.F. Cadastro ),
            Set Selected );
 
//TA DANDO ERRO AQUI MINA
// Join para obter vendas e clientes das cidades
      
(dt1 << Join(
            With( dt2 ),
            By Matching Columns( :Cidades = :Cidades ),
            Drop multiples( 0, 0 ),
            Name( "Include non-matches" )(1, 0),
            Preserve main table order( 1 ),
            output table("Joined Table")
      )
);
 
dt3 = data table("Joined Table");
 
dt3 <<      New Column( "MKT Share", Numeric, Continuous,
            Formula(:Name("Sum(Vlr. Venda Comercial)") / (:Name("Mean(Pot. Consumo Estimado por Categoria (2015) - Manutenção e acessórios/)") * 0.127)),
            Set Selected
      );

 

     

theo_beluzzi
Level I

Re: Joining tables with JSL

It worked! Thanks, man!

How can i rename the dt3?

txnelson
Super User

Re: Joining tables with JSL

dt3 << set name("the new name");

Jim
theo_beluzzi
Level I

Re: Joining tables with JSL

Yaay, thaanks!

txnelson
Super User

Re: Joining tables with JSL

What I suggest, is that you run your script down to where you are going to do the join.  But don't run the join.  Then go into interactive JMP, Tables==>Join   and do the join manually, just the way you want it to be done.  Once that is complete, go to the newly joined data table, and go to the tables panel and click on the "Source" red triangle, and Edit the code.  There you will find the code you need to join the data the way you want.

 

Additionally, I have made some minor changes to your script, which does not change the functionality of the code, but makes the code more precise:

 

 

// open and retain a reference
 
dt = Open("P:\CompComercialDPK\Inteligencia de Mercado DPK\Theodora\Projetos\Áreas de Influência\Estudos Preliminares\Raio 50 KM - DF.xlsx");
 
// criando a coluna de cidades, oncatenando cidade e estado
       dt<< New Column( "Cidades",
              Character,
              Nominal,
              Formula(:Nome || ", " || :Estado ),
              Set Selected
       );
 
// Abre a base de dados de vendas de 2015 
dt2 = Open("P:\CompComercialDPK\Inteligencia de Mercado DPK\Theodora\Projetos\Áreas de Influência\Vendas 2015.xlsx");
 
// criando a coluna de cidades, concatenando cidade e estado
 
       dt2<< New Column( "Cidades",
              Character,
              Nominal,
              Formula(:Desc. Município Cadastro || ", " || :Cód. U.F. Cadastro ),
              Set Selected
       );
 
//TA DANDO ERRO AQUI MINA
 
// Join para obter vendas e clientes das cidades
       dtjoined= dt << Join(
              With( dt2 ),
              By Matching Columns( :Cidades = :Cidades ),
              Drop multiples( 0, 0 ),
              Name( "Include non-matches" )(1, 0),
              Preserve main table order( 1 )
       )
);
       dtjoined<< New Column( "MKT Share",
              Numeric, Continuous, Formula(:Name("Sum(Vlr. Venda Comercial)") /
               (:Name("Mean(Pot. Consumo Estimado por Categoria (2015) - Manutenção e acessórios/)") * 0.127)),
              Set Selected
       );

 

 

Jim