topic Keeping Max values only in QlikView

Keeping Max values only

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

Hi all,

I am new to Qlikview and I need help extracting only max values corresponding to OriginalICERecordID(ReadyPublish) in ICERecordID(ReadyPublish) keeping all the other columns and delete the rows which do not contains max value. Length of these field is 15 char. This data is getting imported from a CSV file.

This is 1 example, file contain thousand of such entries.

Thanks and regards

Abhijit

Re: Keeping Max values only

sunny_talwar — Tue, 06 Mar 2018 16:42:37 GMT

May be do a right join like this

Right Join (FactTable)

LOAD [OriginalICERecordID(ReadyPublish)],

Max([ICERecordID(ReadyPublish)]) as [ICERecordID(ReadyPublish)]

Resident FactTable

Group By [OriginalICERecordID(ReadyPublish)];

Re: Keeping Max values only

shiveshsingh — Tue, 06 Mar 2018 17:01:56 GMT

Get the max value in one table and then do a right join with original one.

It will give the desired result.

If you can share sample data, it would help

Re: Keeping Max values only

Anonymous — Wed, 07 Mar 2018 11:51:39 GMT

Hi stalwar1‌

Thank you for reply, however after executing this code, I am getting the following output.

Regards

Abhijit

Re: Keeping Max values only

sunny_talwar — Wed, 07 Mar 2018 11:53:40 GMT

Can you post the exact script you tried?

Re: Keeping Max values only

Anonymous — Wed, 07 Mar 2018 11:55:31 GMT

Table:

LOAD *

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Right join (Table)

Load (OriginalRecID),

Max(RecID) as RecID

Resident Table

Group by OriginalRecID;

Re: Keeping Max values only

sunny_talwar — Wed, 07 Mar 2018 11:59:56 GMT

Can you try MaxString instead of Max

Right join (Omega)

Load [OriginalICERecordID(ReadyPublish)],

MaxString([ICERecordID(ReadyPublish)]) as [ICERecordID(ReadyPublish)]

Resident Omega

Group by [OriginalICERecordID(ReadyPublish)];

Re: Keeping Max values only

Anonymous — Wed, 07 Mar 2018 12:01:16 GMT

Hi stalwar1‌

You are genius.

Thanks a ton, Mate!