Need to Compared IDs between two Weeks, Month

[#1]

Hi

I have a field with Patient ID and a field with Date, need to compare the Patient ID whether it is available prior week, if it is not there then it is consider as my new add, similarly I compare it with the prior week with the current week if any patient IDs are missing then it is considered as drops.

I have attached an excel with a vlookup comparing two weeks, I need the count of add and drop, I am not successful in achieving this in QV

Please help, if you could give an example it would be great

Regards

Arjun

[#2]

Hi there

You should start off firstly by importing the data as per below, you should assign each patient id a particular week e,g

Table_1:

LOAD
filebasename() as FileName,PATIENT_ID,WEEK

FROM Test.csv (

ansi,

txt,

delimiter

is ',',

embedded

labels,

msq);

Once done you will be able to get the data in a chart and make comparisons as to whether on patient ID exists in one week and not the other.

This could also be done by creating 2 seperate tables in Qlikview for each week you want to compare.

Thanks

AIdan

[#3]

Hi Aldan

I wanted to know how many adds for the current week and drops for the previous week, and further drill down it should give me the list of Patient ID who are missing and are new addition.

can you please help me how would you compare the patient ID in a chart, I have used an expression to get the result but it partially working, I have also attached a qv document

Please help

regards

Arjun

[#4]

Hi Arjun.

Look at this load statement, i've done it for you as example.

In the attached files you can see the source document.

CODE_TO_COPY:

/*

STATEMENT STEPS:

- Load data from source xlsx file and extract useful dimensions from date field

- Generate Variables to set the Mapping LOAD statement wich load specific PATIENT_ID From First Source Table

- Mapping LOAD statements: flag PATIENT_ID under specifc queries (YEAR + WEEK)

- Append mapping fields in source table

- Generate "STATUS" Field as a result of the flags combinations

*/

TempPatient1:

LOAD  PATIENT_ID,

      DATE

    ,Year(DATE)        as YEAR

    ,Num(Month(DATE))  as MONTH

    ,Num(WeekDay(DATE)) as DAY

    ,Num(Week(DATE))    as WEEK

    ,Date(WeekStart(DATE),'DD/MM/YYYY') as WEEK_START_DATE

    ,Date(DATE,'YYYY')&Num(Week(DATE))&Num(WeekDay(DATE)) as DATE_ID    

FROM

[Patient.xlsx]

(ooxml, embedded labels, table is Foglio1);

LastWeek:

LOAD  Max(DATE_ID) as MAX_DATE_ID

    ,Max(YEAR)    as MAX_YEAR

Resident TempPatient1;

LET vMaxWeek = Mid(Peek('MAX_DATE_ID',0,'LastWeek'),5,2);

LET vMaxYear = Peek('MAX_YEAR',0,'LastWeek');

LET vPreviousWeek = $(vMaxWeek)-1;

Drop Table LastWeek;

CurrentPatient:

Mapping LOAD  PATIENT_ID,'1'

Resident TempPatient1

Where YEAR=$(vMaxYear) and WEEK=$(vMaxWeek);

PreviousWeekPatient:

Mapping LOAD  PATIENT_ID,'1'          

Resident TempPatient1

Where YEAR=$(vMaxYear) and WEEK=$(vPreviousWeek);

TempPatient2:

LOAD

*

,ApplyMap('CurrentPatient',PATIENT_ID,0)          as CurrentPatientFlag

,ApplyMap('PreviousWeekPatient',PATIENT_ID,0)    as PreviousWeekPatientFlag

Resident TempPatient1;

Drop Table TempPatient1;

Patient:

LOAD

*

,if(CurrentPatientFlag=1 and PreviousWeekPatientFlag=0

,'New'

  ,if(CurrentPatientFlag=1 and PreviousWeekPatientFlag=1

, 'Old'

,'Resign'))                                      as STATUS

Resident TempPatient2;

Drop Table TempPatient2;

[#5]

Hi Carlo,

Thanks for you example, I tried the logic you have given me, I have created a straight table, and if you see that the positive 1's or my addition (ie a new patient and a patient whos was already with us but for some reason left and again rejoined that is also considered as add .. and all those negative 1s or my drops.

When I compared the STATUS field as per your code it does give me those patients ideally according to my result attached 7 should be my additions and 3 are my drops

Please have a look at the qvw file attached

thank you very much

Arjun

[perumal_41 · Accepted solution]

hi arjun,

PFA ,I hope it help to u.

[#7]

Hi Perumal

thank you very much it worked perfect.

Regards

Arjun