How to write Peek with if else condition for the expression

[dhavalvyas]

Hi,

I have raw data contain site_id,site live date and site close date respectively.

If same site_id will found for next site live date then it should add to count,i.e. now count is 2. but if same site_id found in site close date then it should subtract from count,i.e. its count is now 1

Can any one suggest how to write If Else condition with peek for the same ?

[devarasu07]

Hi,

Can you try like below, Tks

if(site_id = Peek(site_id) and [next site live date] = Peek([next site live date],1,0)) as Flag_Peek

[dhavalvyas]

But what is for site close date  ?

[dhavalvyas]

we have to add it to count if next site live date is found for same site id and also subtract if site close date found for same site id

[devarasu07]

Hi,

can you share your mock data and expected output in excel format. tks

[dhavalvyas]

There is site live date and site close date.Many ATM ID is closed in 2099 and before 2099.Also there is same site ID repeated.So i want total site ID open date wise on selection of month and year.

Suppose from 100 ATM ID on 01/01/2017 10 ATM ID close so it remains with 90 and on 01/02/2017 5 new ATM ID open and 8 ATM ID close then output should be 90+5-8=87 ATM ID.

[Anil_Babu_Samineni]

In away your explanation is more interesting !!! Better, If you provide sample data to test rather simple discussion of the logic behind.

So, To answer your question could be there is number of queries may handle. Do you have Open and Close field with you?

[dhavalvyas]

Ok I have shared it with Raw Data please check.

[Anil_Babu_Samineni]

Not sure, Why opened new thread Peek previous count with If condition

Anyway, In my way let's talk real - My case study is 02-Jan-2017. from that, What it has to be result set?

[dhavalvyas]

Because i am not able to attached raw data file in same thread ? can you suggest any solution for my quey ?