how to merge 2 different sources

[#1]

Hi guys,

I have 2 Sources

S1: //actual

Load  Keynr1,

     Date

Resident X

S2: //archieved

Load Keynr2,

     Versionnr,

     Date

Resident Y

------

I want to have  a clear table with distinct keynr, becaus in S2 (archieved) are some Keynr from S1 ..

and in S2 are many Versions of Keynr2s.. so I need only the lastest Version of each Keynr2

So I've tried:

All:

//actual

Load  Keynr1,

     Date

Resident X

JOIN

//archieved

Load Keynr2,

     Versionnr,

     Date

Resident Y

1: //(1only)

Load Keynr1, Date

     Resident All Where not exists(Keynr2)

2: //(1&2only)

Load Keynr2,Date,max(Versionnr) as Versionnr

        Resident All Where exists(Keynr1) and exists(Keynr2) Group by Keynr2, Date, Versionnr;

3: //(2only)

Load Keynr2 as Keynr3, Date, Max(Versionnr) as Versionnr

     Resident All Where not exists(Keynr1) group by Keynr2, Date, Versionr;

Is that right?

I dont get the 2. table (1&2only)..

can you explain me, and tell me how to filter that ?

[buzzy996]

can u try this way,

All:

//actual

Load  Keynr1 as Keynr1,

         '' as Versionnr,

     Date

Resident X

//archieved

Load Keynr2 as Keynr,

     Versionnr,

     Date

Resident Y

[jonathandienst]

Something like this:

S1: //actual

Load  Keynr1,

     Date

Resident X;

S2:

Load Keynr2,

     Versionnr,

     Date

Resident Y;

Join(S2)

Load Keynr2,

     Max(Versionnr) As MaxVer

Resident S2

Group By Keynr2;

Concatenate(S1)

LOAD KeyNr2 As Keynr1,

  MaxVer As Versionnr,

  Date

Resident S2

Where Not Exists(Keynr1, Keynr2)

  And Versionnr = MaxVer;

HTH

Jonathan

[#4]

Why do you want S2 Join with S2?