8 Replies Latest reply: Mar 30, 2017 2:03 PM by Eduardo DImperio RSS

    Using mapping as an alternative to joining

    Eduardo DImperio

      Hi everyone,

       

      I need some help please.

       

      I have one table with some data and i need to join with another table, but they dont have any field in comum.

      Doing a research i found a way to insert a field in my first table and finally join both, i could do that using mapping function.

      But for somereason the  ApplyMap doesn't recognize the field of my Mapping table.

       

      for a=1 to vInicio

      Let vEndereco = FieldValue('Endereco_Arquivo',$(a));

       

      ADICIONA_NOME:

      Mapping

      LOAD

      Linhas,

      Nome_Arquivo

      Resident LISTA_ARQUIVOS

      WHERE Linhas=$(a);

       

       

      TMP:

      LOAD

       

       

          Data,

          Dia,

          "Postos horários"                                                              AS PICO,

          ("kWh fornecido")                                                              AS CONSUMO,

           ApplyMap('ADICIONA_NOME', Nome_Arquivo, null())      AS Id_Hotel

      FROM [lib://$(vEndereco).qvd](qvd)

      WHERE YEAR(Data) = $(vAno) AND MONTH(Data)=$(vMes);

       

      drop table ADICIONA_NOME;

       

      next

        • Re: Using mapping as an alternative to joining
          Eduardo DImperio

          I was checking about this function and i realize that maybe i need to have in common field between the 2 tables, like regular join. If so, this doesn't help me and i will need help to figure out a way to join the field "Nome_do_Arquivo" from ADICIONA_NOME into TMP.

          • Re: Using mapping as an alternative to joining
            Vineeth Pujari

                 ApplyMap('ADICIONA_NOME', field from table TMP, null())      AS Id_Hotel

              • Re: Using mapping as an alternative to joining
                Eduardo DImperio

                Hi Vineeth,

                 

                As i read the article about, its very clear to me that i need a common field in both tables, and its not the case.

                Right? So how can i input Nome_Arquivo into TMP?

                 

                Other thing, to make clear that i understand this function.

                ApplyMap('ADICIONA_NOME', field from table TMP, null())      AS Id_Hotel

                It will check in 'ADICIONA_NOME' the field from table TMP (first parameter), and return if match Nome_Arquivo (second parameter) with a name that i choose.


                And thank you for your time

                  • Re: Using mapping as an alternative to joining
                    Stefan Wühl

                    You don't need a common field name to link your mapping table with the table you are loading using the ApplyMap().

                     

                    In fact, a mapping table is only a temporary table, it's just a table to lookup values for a given key (key - value pairs).

                    You can query the value by given the key as second argument in ApplyMap() function.

                     

                    I rather think your problem is something different:

                     

                    Resident LISTA_ARQUIVOS

                    WHERE Linhas=$(a);

                     

                    Are you sure this is correct?

                    Have you tried to load the table without the MAPPING prefix and checked that the key - value pairs are loaded correctly?

                     

                    edit: As often, it's much easier to help you if you could provide  a simplified data sample.

                      • Re: Using mapping as an alternative to joining
                        Eduardo DImperio

                        Hi Stefan !

                        I'll try to answer you:

                         

                        1)Are you sure this is correct?

                        Yes, because each TMP refers to one Name in LISTA_ARQUIVOS.

                        LISTA_ARQUIVOS is like a list of names and TMP a list with adress, phone,city etc. So i need related 1:1

                         

                        2)Have you tried to load the table without the MAPPING prefix and checked that the key - value pairs are loaded correctly?

                        Yes. it runs with no problem

                         

                         

                        3)edit: As often, it's much easier to help you if you could provide  a simplified data sample.

                        You mean attach the qvd with data or share a image of output?

                         

                         

                        And i did a workaround, it works but i feel that is not the right way to do. See the code below please


                        for a=1 to vInicio

                        Let vEndereco = FieldValue('Endereco_Arquivo',$(a));

                        Let vArquivo= subfield(vEndereco, '\', 4);

                         

                         

                        ADICIONA_NOME:

                        Mapping

                        LOAD

                        Linhas,

                        Nome_Arquivo

                        Resident LISTA_ARQUIVOS

                        WHERE Linhas=$(a);

                         

                         

                        TMP:

                        LOAD

                            Data,

                            Dia,

                            "Postos horários" AS PICO,

                            ("kWh fornecido") AS CONSUMO,

                            ApplyMap('ADICIONA_NOME', Data,'$(vArquivo)') as Id_Hotel

                        FROM [lib://$(vEndereco).qvd](qvd)

                        WHERE YEAR(Data) = $(vAno) AND MONTH(Data)=$(vMes);

                         

                        next

                         

                        It works, but im not happy with this code.