topic peek fonction ? in App Development

peek fonction ?

QlikTonic — Fri, 17 Sep 2021 15:32:27 GMT

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

Re: peek fonction ?

PrashantSangle — Fri, 17 Sep 2021 15:39:01 GMT

Can you provide sample data?

Re: peek fonction ?

QlikTonic — Fri, 17 Sep 2021 15:54:16 GMT

here you have

thanks

Re: peek fonction ?

PrashantSangle — Fri, 17 Sep 2021 16:28:59 GMT

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

Re: peek fonction ?

MayilVahanan — Sat, 18 Sep 2021 02:04:22 GMT

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:

Re: peek fonction ?

QlikTonic — Sat, 18 Sep 2021 04:14:58 GMT

Thanks a lot Mayil!!!!!