Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - I am trying to set a flag for each token the first and last entry per day
I am current trying using peek to get the results but getting errors
If(Peek(Token)<>Token,Timestamp,If(Peek(FirstFlag),Peek(FirstFlag) ,DateTime)) as FirstFlag,
Token | TImestamp | Desired Flag output |
444733439845528493 | 18-09-2022 08:02 | 1 |
444733439845528493 | 18-09-2022 08:08 | 0 |
444733439845528493 | 18-09-2022 08:09 | 0 |
444733439845528493 | 18-09-2022 08:10 | 0 |
444733439845528493 | 18-09-2022 08:19 | 0 |
444733439845528493 | 18-09-2022 08:34 | 1 |
444733439845528493 | 17-09-2022 04:42 | 1 |
444733439845528493 | 17-09-2022 04:55 | 0 |
444733439845528493 | 17-09-2022 09:18 | 0 |
444733439845528493 | 17-09-2022 09:42 | 0 |
444733439845528493 | 17-09-2022 09:43 | 1 |
17839739266241238273 | 18-09-2022 04:08 | 1 |
17839739266241238273 | 18-09-2022 04:23 | 0 |
17839739266241238273 | 18-09-2022 04:25 | 0 |
17839739266241238273 | 18-09-2022 04:29 | 0 |
17839739266241238273 | 18-09-2022 06:22 | 0 |
17839739266241238273 | 18-09-2022 06:27 | 0 |
17839739266241238273 | 18-09-2022 06:46 | 0 |
17839739266241238273 | 18-09-2022 08:14 | 0 |
17839739266241238273 | 18-09-2022 08:14 | 0 |
17839739266241238273 | 18-09-2022 08:15 | 0 |
17839739266241238273 | 18-09-2022 08:21 | 0 |
17839739266241238273 | 18-09-2022 08:52 | 0 |
17839739266241238273 | 18-09-2022 08:52 | 0 |
17839739266241238273 | 18-09-2022 08:58 | 0 |
17839739266241238273 | 18-09-2022 08:59 | 0 |
17839739266241238273 | 18-09-2022 09:00 | 0 |
17839739266241238273 | 18-09-2022 09:06 | 0 |
17839739266241238273 | 18-09-2022 09:14 | 1 |
Hi @Ironzem
you can try by sorting first in one direction and comparing with previous record.
table2:
load
if(Token <> Previous(Token) or number_date <> Previous(number_date) ,1,0) as flag1,
*;
load
Floor(date#(TImestamp_field,'dd-mm-yyyy hh:mm')) as number_date, //to have date without time
*
resident table1
order by Token, TImestamp_field asc;
drop table table1; //table1 is your previous table
and then sorting in the other way around and comparing again with previous.
table3:
load
if(flag1=1 or Token <> Previous(Token) or number_date <> Previous(number_date),1,0) as FinalFlag,
*
resident table2
order by Token, TImestamp_field desc;
drop fields number_date,flag1; //as are not needed anymore
drop table table2; //previous sorted table, also not needed anymore
Hope it works for you
Please, remember to mark the thread as solved once getting the correct answer
Best,
Hi @Ironzem
you can try by sorting first in one direction and comparing with previous record.
table2:
load
if(Token <> Previous(Token) or number_date <> Previous(number_date) ,1,0) as flag1,
*;
load
Floor(date#(TImestamp_field,'dd-mm-yyyy hh:mm')) as number_date, //to have date without time
*
resident table1
order by Token, TImestamp_field asc;
drop table table1; //table1 is your previous table
and then sorting in the other way around and comparing again with previous.
table3:
load
if(flag1=1 or Token <> Previous(Token) or number_date <> Previous(number_date),1,0) as FinalFlag,
*
resident table2
order by Token, TImestamp_field desc;
drop fields number_date,flag1; //as are not needed anymore
drop table table2; //previous sorted table, also not needed anymore
Hope it works for you
Please, remember to mark the thread as solved once getting the correct answer
Best,
Thank you , worked perfect