topic Re: How to consider max sales by comparing with two fields in QlikView

How to consider max sales by comparing with two fields

rkpatelqlikview — Mon, 11 Jun 2018 05:23:35 GMT

Dear Experts,

Can anyone please suggest on this?

I do have below fields. Here i need to consider the sales based on the CheckID and PID but here PID will get duplicates and checkID unique numbers. At this situation we need to consider the only one sales which is highest number.

Below example:

Store ID : AP345, Check ID are 3 distinct numbers, PID are duplicates. Here the final sales should be 675, which is highest sales.

Thanks in advance.

Re: How to consider max sales by comparing with two fields

sumeet-vaidya — Mon, 11 Jun 2018 06:50:57 GMT

Hi Kumar,

Use below expression in your Set Analysis.

=Sum(Aggr(Max(Sales),StoreID&PID))

Regards

Sumeet

Re: How to consider max sales by comparing with two fields

rkpatelqlikview — Mon, 11 Jun 2018 08:09:49 GMT

Thanks Sumeet,

Can i get this in the back end? I need to do this in script level it self.

Re: How to consider max sales by comparing with two fields

PrashantSangle — Mon, 11 Jun 2018 08:46:24 GMT

what if there date and storeID was different then in such case what you want??

Try below

load * from table;

Left Join

load pid,max(Sales) as max_Sales from table

group by pid;

Regards,

Re: How to consider max sales by comparing with two fields

rkpatelqlikview — Mon, 11 Jun 2018 10:00:36 GMT

Thanks for your response Prashanth,

If there date and storeID are different at that time the PID never get the duplicates.

Re: How to consider max sales by comparing with two fields

rkpatelqlikview — Mon, 11 Jun 2018 10:08:40 GMT

Excellent Prashanth,

It's working properly. Here i do not want to consider the remaining sales. I need to remove those records from that column which are not highest number.

Re: How to consider max sales by comparing with two fields

rkpatelqlikview — Tue, 12 Jun 2018 03:48:41 GMT

Dear Prashanth,

Can i remove the records where the numbers are not highest?

I want to remove this marked records. Can you please suggest on this?

Re: How to consider max sales by comparing with two fields

PrashantSangle — Tue, 12 Jun 2018 05:18:36 GMT

Yes you can after left join you can take resident of that table add new condition into it like

if(Sales=maxSales,1,0) as flag;

then use only those data which flag is 1 in front end.

Also if you want to remove those record in back end then again take Resident of new table and use where clause

like where flag=1;

Re: How to consider max sales by comparing with two fields

rkpatelqlikview — Tue, 12 Jun 2018 06:07:30 GMT

Thanks for your great support Prashanth,

I did with this script but its separated all the records. Please find the attached QVW which i have done.

Re: How to consider max sales by comparing with two fields

PrashantSangle — Tue, 12 Jun 2018 06:39:42 GMT

update below code and try

T1:

LOAD

Date,

StoreID,

CheckID,

PID,

Sales

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join

Load

PID,

Max(Sales) as Maxsales Resident T1 Group by PID;

NoConcatenate

Final:

Load

if(Sales=Maxsales,1,0) as flag Resident T1;

DROP table T1;

Regards,

Re: How to consider max sales by comparing with two fields

rkpatelqlikview — Tue, 12 Jun 2018 08:52:01 GMT

Thanks Prashanth,

It's correct.

I used below one to get the maximum sales. Thanks again.

Final:

Load

if(Sales=Maxsales,1,0) as flag Resident T1 where if(Sales=Maxsales,1,0) =1;

Regard.