peek fonction ?

[QlikTonic]

Hi,

i need your help please.

for a stay in a hotel i have the following data :

Entry	09.07.2020
Exit	12.07.2020

and 

Presence
09.07.2020
09.07.2020
10.07.2020
11.07.2020
12.07.2020

i would like to count 0.5 for the entry date, 0.5 for the exit date and 1 night for the night between entry and exit and 0 if i have several times a presence date.

the result should be :

Presence	Night
09.07.2020	0.5
09.07.2020	0
10.07.2020	1
11.07.2020	1
12.07.2020	0.5

I wrote :

if (
peek(presence,-1)=presence,0,
if(
presence=exit or presence=entry,0.5,1))) as NightCount,

i obtain :

Presence	Result
09.07.2020	0.5
09.07.2020	0.5
10.07.2020	1
11.07.2020	1
12.07.2020	0.5

i spent a lot of hour for something probably easy

thanks for your help

tonic

[PrashantSangle]

Can you provide sample data?

[QlikTonic]

here you have

thanks

[PrashantSangle]

Why there is 2 date for entry? & why not for others?

[MayilVahanan · Accepted solution]

Hi @QlikTonic 

Try like below

DOCsTemp:
LOAD StayNum,
Entry,
Exit
FROM
[D:\Qlik\Com\Example.xlsx]
(ooxml, embedded labels, table is Stay);

Join
LOAD StayNum,
Presence,
RowNo() as Rows
FROM
[D:\Qlik\Com\Example.xlsx]
(ooxml, embedded labels, table is Presence);

Inner Join(DOCsTemp)
Load Distinct
StayNum
Resident DOCsTemp
Where Presence >= Entry and Presence <= Exit;

Final:
LOAD StayNum, Presence, Entry, Exit, If(Night = Previous(Night) and Night = 0.5, 0, Night) as Night;
Load StayNum, Presence, Entry, Exit, If(Presence = Entry or Presence = Exit, 0.5, 1) as Night Resident DOCsTemp order by StayNum, Presence;

DROP Table DOCsTemp;

o/p:

[QlikTonic]

Thanks a lot Mayil!!!!!