topic Flag First and last Entry in QlikView

Flag First and last Entry

Ironzem — Mon, 26 Sep 2022 09:17:16 GMT

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

Re: Flag First and last Entry

RafaelBarrios — Mon, 26 Sep 2022 17:57:53 GMT

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,

Re: Flag First and last Entry

Ironzem — Tue, 27 Sep 2022 18:06:04 GMT

Thank you , worked perfect