How to consider max sales by comparing with two fields

[rkpatelqlikview]

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.

[sumeet-vaidya]

Hi Kumar,

Use below expression in your Set Analysis.

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

Regards

Sumeet

[rkpatelqlikview]

Thanks Sumeet,

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

[PrashantSangle · Accepted solution]

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,

[rkpatelqlikview]

Thanks for your response Prashanth,

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

[rkpatelqlikview]

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.

[rkpatelqlikview]

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?

[PrashantSangle]

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;

[rkpatelqlikview]

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.

[PrashantSangle]

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,