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

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

[vinieme12]

Don't join - use Applymap instead

[vinieme12]

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

[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

[swuehl]

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.

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

[swuehl · Accepted solution]

'It works' means what? You just added a default value to the mapping functions. If you haven't retrieved any values back previously and now you get only the default value, you can simplify it to:

'$(vArquivo)' as Id_Hotel

[eduardo_dimperio]

Oh, thank you !

My first attempt was to do that, but its not working so i thought that to put some value in a table, need to be between tables using join or mapping or concatenate.

But now i saw how silly  i was, i didn't try with this simple quotes.

You solve my problem