How to get maximum values from each straight table into one table

[mahnoor1279]

Hello everyone,

i've created multiple straight tables where each table consist of top 5 investors for specific fund 

i.e

For Fund.no 24927435

INVESTORS	AMOUNT
ABC	2434300
SDEC	694923
CCD
CSMKF	66565
AAA	56446

for Fund.no 155641

INVESTORS	AMOUNT
MFHF	4649649
CVNC	2737464
BDYN	446440
CNGM	84611
CHRUO	56446

And i want the highest value from both tables into a single table

Output

ABC

2434300

MFHF

4649649

[379SSS]

Try this, You can rename the filed names if you get synthetic errors.

maxamount:
load max(AMOUNTA) as Max
Resident 24927435;

LET vMaxA=peek('Max',0,'maxamount');

Drop Table maxamount;

maxamount1:
load max(AMOUNTs) as Max1
Resident 155641;

LET vMaxB=peek('Max1',0,'maxamount1');

Drop Table maxamount1;

Table:
load
INVESTORS,
AMOUNT
Resident 24927435
where AMOUNT = $(vMaxA)
;

Concatenate

load
INVESTORS,
AMOUNT
Resident 155641
where AMOUNT = $(vMaxB)
;

[NiTo]

Hi,

Can you please explain me how Peek functions works here and also why we drop maxamount and maxamount1 after creating a variable?

I am new to qliksense so I want to understand the concept behind this query?

Thanks

[BrunPierre]

Hi, @mahnoor1279 a simple way could be this.

T1:
LOAD INVESTORS, 
     AMOUNT
FROM T1;

Inner Join
LOAD Max(AMOUNT) as AMOUNT
Resident T1;

NoConcatenate
T2:
LOAD INVESTORS, 
     AMOUNT
FROM T2;

Inner Join
LOAD Max(AMOUNT) as AMOUNT
Resident T2;

NoConcatenate
F:
LOAD * Resident T1;
LOAD * Resident T2;

DROP Tables T1, T2;

[NiTo]

hi Brun.

Thanks for the reply. 

I have posted one query on the community page 3days ago and so far no have tried to solve it and it is really important for me to do, as it as part of my assignment. Can you please look into it and advise on how to do it.

Really looking forward to it. Thanks.