topic Re: Joining tables with JSL in Discussions

Joining tables with JSL

theo_beluzzi — Tue, 08 Mar 2016 14:26:07 GMT

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?

Re: Joining tables with JSL

pmroz — Thu, 21 Jun 2018 20:14:44 GMT

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

Re: Joining tables with JSL

txnelson — Thu, 21 Jun 2018 20:15:50 GMT

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

Re: Joining tables with JSL

theo_beluzzi — Tue, 08 Mar 2016 16:58:51 GMT

It worked! Thanks, man!

How can i rename the dt3?

Re: Joining tables with JSL

txnelson — Tue, 08 Mar 2016 17:33:53 GMT

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

Re: Joining tables with JSL

theo_beluzzi — Tue, 08 Mar 2016 17:37:52 GMT

Yaay, thaanks!