joining tables

[suzzymay]

I have 2 sets of tables which i want to join, but i would like to keep only the maximum leased value out of the two records.

can this be done with a join ?

Date,scope,total,leased

1568683617,60.225.128.0/17,32765,200
1568683617,101.173.0.0/18,16381,16381
1568683617,124.180.64.0/20,4093,4093
1568683617,124.180.112.0/20,4093,0
1568683617,124.180.176.0/20,4093,0
1568683617,124.182.96.0/19,8189,0
1568683617,124.182.208.0/21,2045,2045
1568683617,124.182.224.0/19,8189,8189

1568684617,60.225.128.0/17,32765,205
1568684617,101.173.0.0/18,16381,0
1568684617,124.180.64.0/20,4093,100
1568684617,124.180.112.0/20,4093,4093
1568684617,124.180.176.0/20,4093,4093
1568684617,124.182.96.0/19,8189,8189
1568684617,124.182.208.0/21,2045,0
1568684617,124.182.224.0/19,8189,0

Would like to end up with

1568684617,60.225.128.0/17,32765,205
1568683617,101.173.0.0/18,16381,16381
1568683617,124.180.64.0/20,4093,4093
1568684617,124.180.112.0/20,4093,4093
1568684617,124.180.176.0/20,4093,4093
1568684617,124.182.96.0/19,8189,8189
1568683617,124.182.208.0/21,2045,2045
1568683617,124.182.224.0/19,8189,8189

[StarinieriG]

Hi,

you could try in this way:

1) concatenate the 2nd table (this table will be called TEST in the example)

2) then do something like this:

NoConcatenate
TABLE:
LOAD
scope,
total,
Max(leased) AS leased
Resident TEST
Group By
scope,
total
;

Left Join
LOAD
Date,
scope,
total,
leased
Resident TEST;

Drop table TEST;

[miskinmaz]

you can use the first sorted value function.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Aggre...

[Brett_Bleess]

Suzanne, did either of the posts work for you?  If one or both did, be sure to give the poster credit and let others know which one worked by using the Accept as Solution button on the appropriate post.  If you did something different, consider leaving a post on what you did and then mark that post as the solution.  If you are still working on things, leave an update.

Regards,
Brett