Joining tables with JSL

[#1]

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?

[#2 · Accepted solution]

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

[#3]

It worked! Thanks, man!

How can i rename the dt3?

[#4]

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

[#5]

Yaay, thaanks!

[#6]

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