Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Luis171190
Contributor
Contributor

Last record by date

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!

10 Replies
sidhiq91
Specialist
Specialist

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

Luis171190
Contributor
Contributor
Author

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
Champion III
Champion III

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;
Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
mark6505
Partner - Master
Partner - Master

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
Specialist
Specialist

@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_
Creator
Creator

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
Contributor
Contributor
Author

@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_0-1657892524073.png

 

Luis171190
Contributor
Contributor
Author

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

Luis171190
Contributor
Contributor
Author

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

Qlik Sense - Sin título - Tabla - 15 de julio de 2022.png