Last record by date

[Luis171190]

Good afternoon, I wanted to know how to obtain the last two records for each day, for the loading of scripts. This is the table I have.

materials	date
12324199	1/1/2022
12349090	2/1/2022
12390984	3/1/2022
12390985	4/1/2022
12390986	5/1/2022
12391758	6/1/2022
12403498	7/1/2022
12415203	8/1/2022
12419181	1/2/2022
12427423	2/2/2022
12457274	3/2/2022
12494595	4/2/2022
12789952	5/2/2022
11534159	6/2/2022
12076156	7/2/2022
12115855	8/2/2022
12415202	1/3/2022
12151876	2/3/2022
12153645	3/3/2022
12171508	4/3/2022
12427421	5/3/2022
10516043	6/3/2022
11110353	7/3/2022
12947522	8/3/2022

Thank you so much!

[sidhiq91]

@Luis171190  Could you please provide the expected output? Because I see that the "materials" are repeated only once each day. 

[Luis171190]

Sorry, I pass again. Actually I need the last two data for each day of the month, I use the date format 'DD/MM/YYYY'

Material	Date
12331311	1/1/2022
12386332	2/1/2022
12806191	3/1/2022
10434315	4/1/2022
10434317	5/1/2022
10434318	6/1/2022
10434319	7/1/2022
12947119	30/1/2022
10434277	31/1/2022
12383710	1/2/2022
11699473	2/2/2022
10664941	3/2/2022
11292224	4/2/2022
12643147	5/2/2022
10434280	6/2/2022
10674218	7/2/2022
12403498	27/2/2022
12789952	28/2/2022
12415202	1/3/2022
12151876	2/3/2022
10516043	3/3/2022
12947814	4/3/2022
13009943	5/3/2022
10434326	6/3/2022
12331312	7/3/2022
30029419	30/3/2022
30022421	31/3/2022

Expented result

material	date
12947119	30/1/2022
10434277	31/1/2022
12403498	27/2/2022
12789952	28/2/2022
30029419	30/3/2022
30022421	31/3/2022

Thanks a lot!!

[vinieme12]

temp:
load materials
,Date#(date,'DD/MM/YYYY') as DATE
,if(Day(Date#(date,'DD/MM/YYYY')+2)=1 or day(Date#(date,'DD/MM/YYYY')+1)=1,1,0) as keep
inline [
materials,date
12331311,1/1/2022
12386332,2/1/2022
12806191,3/1/2022
10434315,4/1/2022
10434317,5/1/2022
10434318,6/1/2022
10434319,7/1/2022
12947119,30/1/2022
10434277,31/1/2022
12383710,1/2/2022
11699473,2/2/2022
10664941,3/2/2022
11292224,4/2/2022
12643147,5/2/2022
10434280,6/2/2022
10674218,7/2/2022
12403498,27/2/2022
12789952,28/2/2022
12415202,1/3/2022
12151876,2/3/2022
10516043,3/3/2022
12947814,4/3/2022
13009943,5/3/2022
10434326,6/3/2022
12331312,7/3/2022
30029419,30/3/2022
30022421,31/3/2022];

Inner Join(temp)
Load 1 as keep
AutoGenerate 1;
drop field keep;

[Mark_Little]

Hi there.

I would be looking at the Using the Peek function and Group by on Load to flag there values i was interested.  

[sidhiq91]

@Luis171190  Please see the below code that I have used in the script:

NoConcatenate
Temp:
LOAD Material,
Date#(Date,'DD/MM/YYYY') as Date

inline [
Material, Date
12331311, 01/01/2022
12386332, 02/01/2022
12806191, 03/01/2022
10434315, 04/01/2022
10434317, 05/01/2022
10434318, 06/01/2022
10434319, 07/01/2022
12947119, 30/01/2022
10434277, 31/01/2022
12383710, 01/02/2022
11699473, 02/02/2022
10664941, 03/02/2022
11292224, 04/02/2022
12643147, 05/02/2022
10434280, 06/02/2022
10674218, 07/02/2022
12403498, 27/02/2022
12789952, 28/02/2022
];

NoConcatenate
Temp1:
Load *
where match(Condition,'Retain');
Load *,

if(Day(Date+2)=1 or Day(Date+1)=1,'Retain','Remove') as Condition
Resident Temp;

Drop table Temp;

Exit Script;

If this resolves your issue, please like and accept it as a solution.

[Iswarya_]

Hi @Luis171190 , Please find the script below:

Temp:
Load
Material,
Date(Date,'DD/MM/YYYY') as Date
Inline [
Material, Date
12331311, 1/1/2022
12386332, 2/1/2022
12806191, 3/1/2022
10434315, 4/1/2022
10434317, 5/1/2022
10434318, 6/1/2022
10434319, 7/1/2022
12947119, 30/1/2022
10434277, 31/1/2022
12383710, 1/2/2022
11699473, 2/2/2022
10664941, 3/2/2022
11292224, 4/2/2022
12643147, 5/2/2022
10434280, 6/2/2022
10674218, 7/2/2022
12403498, 27/2/2022
12789952, 28/2/2022
12415202, 1/3/2022
12151876, 2/3/2022
10516043, 3/3/2022
12947814, 4/3/2022
13009943, 5/3/2022
10434326, 6/3/2022
12331312, 7/3/2022
30029419, 30/3/2022
30022421, 31/3/2022
];

NoConcatenate
Final:
Load *
where Flag='1';

Load
Material,
Date,
if(num(Date)=floor(num(MonthEnd(Date))) or num(Date)=floor(num(Date((MonthEnd(Date)-1)))),1,0) as Flag
Resident Temp;
dROP Table Temp;
exit Script;

[Luis171190]

@sidhiq91 I copied what you did but I don't get the same result, it doesn't show me the dates correctly and I'm missing the month of March, sorry I'm new to Qlik Sense

[Luis171190]

I copied everything you passed me but I get blank data, sorry I'm new to Qlik Sense.

[Luis171190]

Sorry, I'm new to qlik sense, the code you gave me isn't working, could you help me? I'll pass you what I get