Joining 2 tables

[raghuvr33]

I need to join two tables getting only the latest record from second table which has multiple records matching with first table. Below is the data for the two tables and data needed for the final table.

Table A:

A1 B1 C1

Table B:

A1 B1 C1 D1

A1 B1 C1 D2

A1 B1 C1 D3

Final Table:

A1 B1 C1 D3

The data in the final table should be 1 row having four columns with the latest data  D3  in table B.

How would we go about doing this in QV?

[MK_QSL]

TableA:

Load * From TableName

TableB:

Load * From TableName

NoConcatenate

FinalTable:

Left Join (TableA) Load * Resident TableB;

Drop Table TableB;

[raghuvr33]

Thanks for the quick response Manish. The solution you recomend, would that not create 3 records in the final table ? What i need is one record with the latest data from table b in the final table as below

Final Table:

A1 B1 C1 D3

[MK_QSL]

TableA:

Load * From TableName

TableB:

Load *, RowNo() as ID From TableName

NoConcatenate

FinalTable:

Left Join (TableA) Load A1, B1, C1, FirstSortedValue(D1, -ID) Resident TableB;

Drop Table TableB;

[rubenmarin]

Hi Raghu, you can create a mapping table sorted by the inverse order of load, something like:

TableB:

NoConcatenate LOAD A, B, C, D, RowNo() as Row... --> Row defines the order or data

MAP_D:

Mapping LOAD A & B & C, D Resident TableB Order By Row desc; --> Create a Map where the first occurrence is the last record (sorted by row desc)

TableA:

NoConcatenate LOAD *, ApplyMap('MAP_D', A & B & C, Null()) as D...

DROP TableB;

[maxgro]

I think you only need a group by (if latest is the max of d)

[Table A]:

load * inline [

a,b,c

A1, B1, C1

];

[Table B]:

load * inline [

a,b,c,d

A1, B1 ,C1, D1

A1 ,B1 ,C1 ,D2

A1 ,B1, C1 ,D3

];

left join ([Table A])

load

a,b,c, MaxString(d) as d

Resident [Table B]

group by a,b,c;

drop table [Table B];

[raghuvr33]

Thanks for the responses Manish  ,Ruben, Massimo. I am thinking your suggestions would work and am going to try it out. Since both tables have a lot of columns (Source is ERP system) in real life , i have a lot to do with your recomended solution.

I will try it out and get back to you.

[raghuvr33]

I have been working on implementing Massimo's solution recomended but getting errors as below

Getting script error 'Invalid expression' with the following statement

Left join (Invoice_Details)

  load

   InvNumlnk,                                  // This is A in above example

   [Invoice Sales Order],                 //  This is b in above example

   %ItemKey,                                  //  This is c in above example

   max([Transaction Effective Date]), //  This is d in above example

    [Transaction Material Cost],

    [Transaction Labor Cost],

    [Transaction Burden Cost],

    [Transaction Overhead Cost],

    [Transaction Subcontract Cost]

Appreciate if you can let me know what i am doing wrong

[maxgro]

max needs a group by

load a,b,c,d,e,

max(...), min(...)

resident ...

group by a,b,c,d,e;

[raghuvr33]

I did not copy the complete script when i posted above. I do have the resident table and group by but still get the error

Join (Invoice_Details)

  load

   InvNumlnk,

   [Invoice Sales Order],

   %ItemKey,

   // max([Transaction Effective Date]),

    [Transaction Material Cost],

    [Transaction Labor Cost],

    [Transaction Burden Cost],

    [Transaction Overhead Cost],

    [Transaction Subcontract Cost]

    Resident Trans_Costs

     Group by InvNumlnk, [Invoice Sales Order], %ItemKey;