Help with filtering Data in Qlik

[Shivam22]

Hi All,

Can anyone please help me with below

my sample data look like below

Sample Data:

Date             ID        Amount      Name
1/1/2025      AAA          2            John
2/1/2025     AAA           5            John
12/1/2025    AAA          2            John
12/1/2025     AAA        5             John
12/1/2025     AAA        5           John
23/1/2025     AAA       2            John
24/1/2025     AAA       5            John
2/1/2025       BBB      10           Sam
2/2/2025       BBB        5           Sam
2/3/2025       BBB       10          Sam
2/3/2025        BBB       10        Sam
2/3/2025        BBB        5          Sam
1/4/2025       CCC        28       John
1/31/2025      CCC       5         John
2/4/2025         CCC      28        John
4/5/2025        CCC       5           John

ETC...

How to get a below output where i can only get line items with MAX Amount for a given date, ID and Name 

OutPut Data:

Date             ID        Amount      Name
2/1/2025     AAA           5            John
12/1/2025     AAA        5             John
12/1/2025     AAA        5           John
24/1/2025     AAA       5            John
2/1/2025       BBB      10           Sam
2/3/2025       BBB       10          Sam
2/3/2025        BBB       10        Sam
1/4/2025       CCC        28       John
2/4/2025         CCC      28        John

Thanks, a lot in Advance

[hanna_choi]

Hello @Shivam22 

How about this?

Step 1
You make a unique number in the modeling.

        

Step 2
You apply the unique number to the dimensions and measure.

[Daniel_Castella]

Hi @Shivam22 

I'm not sure if I'm understanding your request. Do you want to separate the data in the backend or in the front end?

-If it is the backend, it is enough to use the Where clause, like in this code (I renamed the fields to avoid synthetic keys, but if you want to store the tables it is enough by storing and dropping them without a rename needed):

Sample:

LOAD Date, ID, Amount

INLINE [

Date, ID, Amount

1/1/2025, AAA, 2

2/1/2025, AAA, 5

12/1/2025, AAA, 2

12/1/2025, AAA, 5

12/1/2025, AAA, 2

23/1/2025, AAA, 2

24/1/2025, AAA, 5

];

Output1:

NoConcatenate

LOAD Date as Date1, 

     ID as ID1, 

     Amount as Amount1

RESIDENT Sample

WHERE Amount='2';

Output2:

NoConcatenate

LOAD Date as Date2, 

      ID as ID2, 

     Amount as Amount2

RESIDENT Sample

WHERE Amount='5';

-If you want to obtain two tables in the front end, it is just needed to filter the Amount field with an If condition like in this image:

If none of these solutions work for you, could you, please, provide more information about what you are trying to accomplish?

Kind Regards

Daniel

[Shivam22]

Thanks for your reply, but i cannot use hardcoded values like 2 and 5 because there are no many values in our data model.

[marcus_sommer]

It would be useful if you elaborate in more details what the aim behind the sub-sets is. Separating them in n tables is usually not expedient. 

[Shivam22]

Thanks a lot for your reply

Yes,  i  have modified my post.

[marcus_sommer]

You may try:

load Date, ID, Name, max(Amount) as Amount
from X group by Date, ID, Name;

[Daniel_Castella]

Hi @Shivam22 

Try this code, it returns the lines you expect as displayed in the image below:

Sample:

LOAD Date, ID, Amount, Name

INLINE [

Date, ID, Amount, Name

1/1/2025, AAA, 2, John

2/1/2025, AAA, 5, John

12/1/2025, AAA, 2, John

12/1/2025, AAA, 5, John

12/1/2025, AAA , 5, John

23/1/2025, AAA, 2, John

24/1/2025, AAA, 5, John

2/1/2025, BBB, 10, Sam

2/2/2025, BBB, 5, Sam

2/3/2025, BBB, 10, Sam

2/3/2025, BBB, 10, Sam

2/3/2025, BBB, 5, Sam

1/4/2025, CCC, 28, John

1/31/2025, CCC, 5, John

2/4/2025, CCC, 28, John

4/5/2025, CCC, 5, John

];

Inter:

NoConcatenate

LOAD ID, 

max(Amount) as Amount

RESIDENT Sample

GROUP BY ID;

Output:

NoConcatenate

LOAD Date, ID, Amount, Name

RESIDENT Sample;

INNER JOIN (Output)

LOAD ID, Amount

RESIDENT Inter;

DROP TABLE Sample, Inter;

Kind Regards

Daniel

[Daniel_Castella]

Actually, I realized there is a more compact code that provides the same result:

Sample:
LOAD Date, ID, Amount, Name

INLINE [

Date, ID, Amount, Name
1/1/2025, AAA, 2, John
2/1/2025, AAA, 5, John
12/1/2025, AAA, 2, John
12/1/2025, AAA, 5, John
12/1/2025, AAA , 5, John
23/1/2025, AAA, 2, John
24/1/2025, AAA, 5, John
2/1/2025, BBB, 10, Sam
2/2/2025, BBB, 5, Sam
2/3/2025, BBB, 10, Sam
2/3/2025, BBB, 10, Sam
2/3/2025, BBB, 5, Sam
1/4/2025, CCC, 28, John
1/31/2025, CCC, 5, John
2/4/2025, CCC, 28, John
4/5/2025, CCC, 5, John

];

INNER JOIN(Sample)
LOAD ID,
max(Amount) as Amount
RESIDENT Sample
GROUP BY ID;