lag function to see if there is a same record

[sai_12]

Hi i am trying to solve  a problem which is something like below picture,

lets say we have a client we want to see he has gone back to a hospital where he was previously serviced at after going to another hospital,

Here client a goes to harvard but never comes back to harvard after moving to harvard

but he goes back to ucla ,after seeing lsu again.

i hope you understand,

All want to see is if there is a readmission in the same hospital , where client was given service initially, and there are multiple clients

we have service from always ,but service to can be null 

Thanks in advance

(in front end or backend)

Problem:

Expected output:

[rakeshkumar1890]

Hi Sai,

Hope this script would work for you

TableA:

Load * Inline [
Client , School
A, Harvard
A, Harvard
A, Oxford
A, MIT
A, Harvard
];

Left Join (TableA)
Load * Inline [
School, Flag
Harvard, Y
];

Admission:
Load *,
if(IsNull(Flag),'N','Y') as ReadmissionFlag
Resident TableA;

Drop Table TableA;
Drop Field Flag;

[marcus_sommer]

Is the shown output really the wanted output? Because if the flag could be created with a query of count(school)>1 respectively a distinct load.

If not and each origin record should get the flag you need another approach and your mentioned previous() should work well for it - but you will need a properly sorted resident-load for it, maybe like:

load client, date, school, if(client = previous(client), if(school = previous(school), 1, 0), 0) as Flag
resident source order by client, date;

assuming that there appropriate start- and end-dates. 

[Chanty4u]

Hi try below 

// Create a sample table with the client's school attendance history

ClientSchools:

LOAD * INLINE [

    Client, School

    A, Harvard

    A, Yale

    A, Princeton

    A, Harvard

    A, Stanford

    A, Harvard

];

// Sort the table by the client and the order in which they attended schools

ClientSchools:

SORT BY Client ASC, SchoolOrder ASC;

// Create a new table that will flag any instances of readmission

Readmission:

LOAD

    Client,

    School,

    PreviousSchool,

    If(PreviousSchool = 'Harvard', 'Continuous', If(School = 'Harvard', 'First', 'Readmission')) AS HarvardStatus

RESIDENT

(

    SELECT

        Client,

        School,

        Peek('School') AS PreviousSchool,

        RowNo() AS SchoolOrder

    FROM ClientSchools

    WHERE Client = 'A'

) Temp;

// Output the results to a table that can be used for analysis

OUTPUT Readmission TO ReadmissionTable;

[MarcoWedel]

another solution might be:

table1:
LOAD *,
     If(Sum((Client=Previous(Client) and School=Previous(School))+1)>1,'yes','no') as ReadmissionFlag
Inline [
Client, School
A, School1
A, School1
A, School2
A, School3
A, School1
B, School1
B, School2
B, School3
C, School2
C, School3
C, School3
C, School2
D, School2
D, School1
D, School3
D, School2
D, School2
D, School3
D, School1
D, School2
]
Group By Client, School;

[sai_12]

hi , thank you for your response , i think may be i have to be more clear , i have attached a sample file ,

lets say we have a client we want to see he has gone back to a hospital where he was previously serviced at after going to another hospital,

Here client a goes to harvard but never comes back to harvard after moving to harvard

but he goes back to ucla ,after seeing lsu again.

i hope you understand,

All want to see is if there is a readmission in the same hospital , where client was given service initially, and there are multiple clients

the solution you provided works fine for single client but not for multiple ?

Expected output:

[sai_12]

Hi ,thank you so much for your response i have added my requirement with sample data and expected pictures,

[vinieme12]

i think this should be enough

=if(count(Distinct TOTAL <client> hospital&ServiceDate)>1,'Yes','No)

[sai_12]

hi your solution  gives yes for all records, if possible can you please write it correct syntax 

[vinieme12]

add hospitalname as well in aggregation scope

=if(
count(distinct total <ClientName,[hospital name]> ClientName&[hospital name]&Service_From)>1,'Yes','No')