topic Looping and creating a Flag in QlikView

Looping and creating a Flag

Thu, 22 May 2014 13:53:18 GMT

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?

Re: Looping and creating a Flag

Thu, 22 May 2014 13:59:22 GMT

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

Re: Looping and creating a Flag

Thu, 22 May 2014 14:05:08 GMT

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

Re: Looping and creating a Flag

Thu, 22 May 2014 14:06:48 GMT

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

Re: Looping and creating a Flag

preminqlik — Thu, 22 May 2014 14:40:51 GMT

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;

Re: Looping and creating a Flag

maxgro — Thu, 22 May 2014 18:06:39 GMT

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;

Re: Looping and creating a Flag

MarcoWedel — Mon, 26 May 2014 20:36:41 GMT

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