JOIN doesn't work the way it is supposed to...

[datanibbler]

Hi,

I have two tables that I want to join on two fields, [DATE] and [gate]. The issue is

- In [gate], I have one more distinct value in the first table than I have in the second.

- I want to keep those

=> LEFT JOIN should be what I need here, shouldn't it?

<=> I have tried both LEFT and OUTER and in both cases, when I load the created qvd_file into another app, that value is dropped.

Can somebody tell me what's wrong here?

Thanks a lot!

Best regards,

DataNibbler

[ashfaq_haseeb]

Hi you can go with apply map instead.

Regards

ASHFAQ

[datanibbler]

Aha - can you tell me how an ApplyMap() can do the same job as a JOIN between two tables (where every table has more than 2 columns)?

Thanks a lot!

Best regards,

DataNibbler

[jerem1234]

Can you post your code here? or post an example demonstrating the problem? A Left Join should keep all your key field values and just append the appropriate new columns in your original table. So it seems like we will have to dive into your code.

[Michiel_QV_Fan]

You gave the answer yourself. It can't with applymap.

However, you could loop through more than 1 column with a for each statement.

Or create mulitple applymap scripts. (a one time action??)

[Anonymous]

I wonder if you're using the correct order for the tables?

Check the following code:

LeftTable:

LOAD * INLINE [

    Id, Fk, TestField

    1, 3, aaa

    2, , bbb

    3, 2, ccc

    4, , ddd

    5, 2, eee

    6, , fff

    7, 2, ggg

    8, 1, hhh

    9, , iii

    10, 5, jjj

];

RightTable:

LOAD * INLINE [

    IdRT, Test2

    1, abc

    2, def

    3, ghi

    4, jkl

    5, mno

];

Left Join(LeftTable)

Load IdRT as Fk, Test2 as ResultField2

  Resident RightTable;

Note you can use the implicit syntax, if you don't wish keep the RightTable.

The implicit syntax always applies the Join to the previously loaded table.

Good Luck!

[ashfaq_haseeb]

You need to create multiple apply maps

Regards

ASHFAQ