How to find the last record of each month(Set Analysis LOAD) in Qlik Sense

[Paula1]

I'm new to qlik sense and I need the last record of the month (SET ANALYSIS / LOAD)

Produto	MESANOX	PRECOUNITARIO	DATAHORAXICARA
19	fev 2022	8,95	09/02/2022 16:13:44
19	fev 2022	8,1003	11/02/2022 20:53:16
20	jan 2022	6,3525	25/01/2022 23:00:30
20	jan 2022	6,3525	28/01/2022 21:03:47
45	dez 2021	6,3525	29/12/2021 23:15:20
45	dez 2021	6,3525	31/12/2021 11:30:18

Result I need

Produto	MESANOX	PRECOUNITARIO	DATAHORAXICARA
19	fev 2022	8,1003	11/02/2022 20:53:16
19	jan 2022	6,3525	28/01/2022 21:03:47
19	dez 2021	6,3525	31/12/2021 11:30:18

[rubenmarin · Accepted solution]

Hi, while loading data you can add a step to flag the last record of each month.

You can do a resident load with an "order by  DATAHORAXICARA Desc", and the flag can be created as: 

If(Peek(MESANOX)=MESANOX, 0, 1) as isLastRecord

Then you can add a condition to remove the records you don't want either in script or in an expression.

If you can't modify your script you can also try with FirstSortedValue() function.

[Paula1]

You would have an idea how it would look in the load (script). I didn't understand much a thousand apologies

[rubenmarin · Accepted solution]

Hi, I missed your answer, I think you already have found the way to add it.

It would be something like:

NewTableName:
LOAD
  <FieldList>,
  If(Peek(MESANOX)=MESANOX, 0, 1) as isLastRecord
Resident OldTableName
order by  DATAHORAXICARA Desc;

DROP Table OldTableName;

[Paula1]

THANK YOU SO MUCH RUBENMARIN

[Paula1]

RUBENMARIN

If the friend can help a little more beginner friend. I have this problem.

I need to create aggr with FirstSortedValue I can't