Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
@Luis171190 Could you please provide the expected output? Because I see that the "materials" are repeated only once each day.
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!!
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;
Hi there.
I would be looking at the Using the Peek function and Group by on Load to flag there values i was interested.
@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.
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;
@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
I copied everything you passed me but I get blank data, sorry I'm new to Qlik Sense.
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