Subscribe Bookmark RSS Feed

Joining tables with JSL

theo_beluzzi

Community Trekker

Joined:

Mar 8, 2016

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

      );

     

5 REPLIES
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

      );

     

theo_beluzzi

Community Trekker

Joined:

Mar 8, 2016

It worked! Thanks, man!

How can i rename the dt3?

txnelson

Super User

Joined:

Jun 22, 2012

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

Jim
theo_beluzzi

Community Trekker

Joined:

Mar 8, 2016

Yaay, thaanks!

txnelson

Super User

Joined:

Jun 22, 2012

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