Looping and creating a Flag

[#1]

I have a file that consists of many different people with many different antibiotics they received several times a day during their visit.

I am not sure exactly how to program this formula, but I think I need some kind of loop statement.

Example:

Rec Num               Name                    Antibiotic     Admin Date/Time

5551212                John Q Public     Vanco             5/05/2014 0800

5551212                John Q Public     Vanco              5/05/2014 1600

5551212                John Q Public     Ancef              05/05/2014 2200

5551213                Winnie Bago       PCN                 05/05/2014 0900

5551212                John Q Public     Vanco             5/06/2014 0800

5551212                John Q Public     Vanco             5/06/2014 1600

Expected Result: 

Rec Number       Name    Antibiotic             Date administered          Flag

5551212                John Q Public     Vanco    5/05/2014 0800               Yes          1

5551212                John Q Public     Vanco     5/05/2014 1600                No           0

5551212                John Q Public     Ancef     05/05/2014 2200             Yes           1

5551213                Winnie Bago       PCN         05/05/2014 0900             Yes         1

5551212                John Q Public     Vanco     5/06/2014 0800               Yes           1

5551212                John Q Public     Vanco     5/06/2014 1600               No             0

For each distinct record number AND Antibiotic and DAY, create a column and populate with a number 1(as a flag).   I need to count a single occurrence of a single kind of antibiotic per day/ per patient.   Multiple doses of the same antibiotic per same patient and day count as 1.

Can anyone help me with this?

[#2]

In load script you may order your rows by Name and Antybiotic and add Name&';'&Antybiotic as newfield

Then use if (peek('newfield')) =Name&';'&Antybiotic, 0,1) as flag

regards

Darek

[#3]

Neal:

first you have to load the table:

Table:

load *

from ...

then you have to order and create the flag:

table_2:

noconcatenate

load *,

      if(RecNumber = peek('RecNumber') and antibiotic = peek('antibiotic') and date(admindate_time) = peek('date(admindate_time'') , 0 , 1) as FLAG

resident Table

order by RecNumber, antibiotic, admindate_time;

drop table Table;

regards,

Marcelo

[#4]

Add also Date administered into order by and Subfield([Date administered],' ',1) into newfield and if condition.

[preminqlik]

hi there , try this , find attachment

Load *,

if(isnum(CLOSINGDATE),1,0) as Numflag,

if(isnum(CLOSINGDATE),'Yes','No') as Textflag;

Load *,

if(peek([Rec Num])=[Rec Num] and peek(Name)=Name and peek(Antibiotic)=Antibiotic ,peek(CLOSINGDATE)&FINAL_DATE,FINAL_DATE) as CLOSINGDATE;

Load *,

date(Date#([Admin Date/Time],'DD/MM/YYYY hhmm'))     as     FINAL_DATE

from path;

[maxgro]

another one

source:
load *,
date(floor(Date#([Admin Date/Time], 'DD/MM/YYYY hhmm'))) as Date2,
rowno() as id;
Load *
INLINE [
    Rec Num, Name, Antibiotic,Admin Date/Time
    5551212                , John Q Public,  Vanco, 5/05/2014 0800
    5551212                , John Q Public, Vanco              ,  5/05/2014 1600
    5551212                , John Q Public, Ancef              ,  05/05/2014 2200
    5551213                , Winnie Bago, PCN                , 05/05/2014 0900
    5551212                , John Q Public, Vanco            ,  5/06/2014 0800
    5551212                , John Q Public, Vanco            , 5/06/2014 1600
];

left join (source)
LOAD
  [Rec Num], Antibiotic, Date2,
  max(id)as id,
  max(1) as flag
Resident
  source
Group by
  [Rec Num], Antibiotic, Date2;

[MarcoWedel]

Hi Neal,

if it is the distinct count of different antibiotics per patient and day you're interested in, I don't feel there is any need of a flag column at all.

Maybe an additional date column and pivot tables with some distinct count expressions are sufficient?

tabMedication:
LOAD
  *,
  DayName([Admin Date/Time]) as [Date administered];
LOAD
  [Rec Num],
  Name,
  Antibiotic,
  Timestamp#([Admin Date/Time],'MM/DD/YYYY hhmm') as [Admin Date/Time]
INLINE [
    Rec Num, Name, Antibiotic, Admin Date/Time
    5551212, John Q Public, Vanco, 5/05/2014 0800
    5551212, John Q Public, Vanco, 5/05/2014 1600
    5551212, John Q Public, Ancef, 05/05/2014 2200
    5551213, Winnie Bago, PCN, 05/05/2014 0900
    5551212, John Q Public, Vanco, 5/06/2014 0800
    5551212, John Q Public, Vanco, 5/06/2014 1600
];

hope this helps

regards

Marco