topic Re: Joining 2 tables in QlikView

Joining 2 tables

raghuvr33 — Tue, 16 Sep 2014 14:00:16 GMT

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?

Re: Joining 2 tables

MK_QSL — Tue, 16 Sep 2014 14:03:01 GMT

TableA:

Load * From TableName

TableB:

Load * From TableName

NoConcatenate

FinalTable:

Left Join (TableA) Load * Resident TableB;

Drop Table TableB;

Re: Joining 2 tables

raghuvr33 — Tue, 16 Sep 2014 14:13:11 GMT

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

Re: Joining 2 tables

MK_QSL — Tue, 16 Sep 2014 14:17:11 GMT

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;

Re: Joining 2 tables

rubenmarin — Tue, 16 Sep 2014 14:17:35 GMT

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;

Re: Joining 2 tables

maxgro — Tue, 16 Sep 2014 14:30:07 GMT

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];

Re: Joining 2 tables

raghuvr33 — Tue, 16 Sep 2014 14:33:51 GMT

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.

Re: Joining 2 tables

raghuvr33 — Tue, 16 Sep 2014 20:02:00 GMT

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

Re: Joining 2 tables

maxgro — Tue, 16 Sep 2014 20:14:23 GMT

max needs a group by

load a,b,c,d,e,

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

resident ...

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

Re: Joining 2 tables

raghuvr33 — Tue, 16 Sep 2014 20:35:38 GMT

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;

Re: Joining 2 tables

raghuvr33 — Tue, 16 Sep 2014 20:36:40 GMT

The max is not commented in my actual script. Was trying something when i copied above

Re: Joining 2 tables

maxgro — Tue, 16 Sep 2014 21:06:59 GMT

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

Re: Joining 2 tables

raghuvr33 — Tue, 16 Sep 2014 21:57:01 GMT

This worked without giving error when i included all the fields in the group by statement.

Thanks for your help!!