topic Re: joining tables in QlikView

joining tables

suzzymay — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: joining tables

StarinieriG — Tue, 17 Sep 2019 07:29:12 GMT

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;

Re: joining tables

miskinmaz — Tue, 17 Sep 2019 07:30:35 GMT

you can use the first sorted value function.

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

Re: joining tables

Brett_Bleess — Thu, 26 Sep 2019 20:55:22 GMT

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