Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to merge 2 different sources

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 ?

3 Replies
buzzy996
Master II
Master II

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Why do you want S2 Join with S2?