Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
raghuvr33
Contributor III
Contributor III

Joining 2 tables

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?

1 Solution

Accepted Solutions
maxgro
MVP
MVP

this works

load a,b,c,d,e,

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

resident ...

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


this doesn't work

load a,b,c,d,e,

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

resident ...

group by a,b,c;          // fields not in max, min, ..... need group by



View solution in original post

12 Replies
MK_QSL
MVP
MVP

TableA:

Load * From TableName

TableB:

Load * From TableName

NoConcatenate

FinalTable:

Left Join (TableA) Load * Resident TableB;

Drop Table TableB;

raghuvr33
Contributor III
Contributor III
Author


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

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

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author


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

max needs a group by

load a,b,c,d,e,

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

resident ...

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

raghuvr33
Contributor III
Contributor III
Author

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;