COUNT CHANGE BETWEEN TWO DATES

[gabroteddy]

Hi at all,

i want count how many times a patient change therapy.

I have a table like this (i attach the excel file):

NUMMOV	PATIENT	DESCR	DATA_DEL	PHARMA_CODE
67489	PROVA PAZIENTE1	ZELITREX*21 CPR RIV. 1000 MG	19/12/2017	029503024
67490	PROVA PAZIENTE2	KIVEXA*600+300MG 30 CPR	18/12/2018	036644019
67491	PROVA PAZIENTE2	ISENTRESS*400MG 60CPR	18/12/2018	038312017
67492	PROVA PAZIENTE3	ISENTRESS*400MG 60CPR	14/12/2019	038312017
67493	PROVA PAZIENTE1	TIVICAY*30CPR RIV 50MG	14/01/2020	043195015
67494	PROVA PAZIENTE1	LAMIVUDINA MY*60CPR RIV 150MG	14/01/2020	040485029
67495	PROVA PAZIENTE2	KIVEXA*600+300MG 30 CPR	15/01/2020	036644019
67496	PROVA PAZIENTE2	ISENTRESS*400MG 60CPR	16/01/2020	038312017
67497	PROVA PAZIENTE1	CELSENTRI*300MG 60CPR RIV	14/02/2020	038138083
67498	PROVA PAZIENTE3	ISENTRESS*400MG 60CPR	15/02/2020	038312017
67499	PROVA PAZIENTE2	ZELITREX*21 CPR RIV. 1000 MG	16/02/2020	029503024

I want to count whenever a  PATIENT has therapy changes (DIFFERENT PHARMA_CODE) between two delivery dates (DATA_DEL).

In the example the 

PROVA PAZIENTE1 have n.3 CHANGES

PROVA PAZIENTE2 have n.1 CHANGE

PROVA PAZIENTE3 have n.0 CHANGE

Can you give me the correct expression and script for count these occurences?

[jochem_zw]

make table with PATIENT as dimension and count(distinct DESCR) as measure

DATA_DEL can be a selection filterbox.

Should do the job. 

[gabroteddy]

Hi jochem_zw,

your answer not resolve my problem.

I need to count only if the patient has changed therapy since the previous date...whith an expression like

count( distinct Aggr(FA_DATA_EROG, PAZIENTE)) if AP_MINSAN10 <> AP_MINSAN10 in previous FA_DATA_EROG 

and not count anything if is the frist FA_DATA_EROG.

I apologize but i don't know how i can formulate this expression correctly.

[jochem_zw]

did some quick scripting (bit dirty), there should be a more simple method, but lack of time! Result should be ok. GL

Source:
LOAD
NUMMOV,
PAZIENTE,
PAZIENTE&'~'&FA_DATA_EROG as Key,
DESCR,
FA_DATA_EROG,
AP_MINSAN10
FROM [D:\Community.xlsx] (ooxml, embedded labels, table is Blad2);

NoConcatenate
TMP:
Load
PAZIENTE,
FA_DATA_EROG,
Key,
Concat(DESCR,',') as ConCatDescr,
Count(DESCR) as #DESCR
Resident Source
Group By PAZIENTE,FA_DATA_EROG,Key;

NoConcatenate
ResultTMP:
Load
PAZIENTE,
FA_DATA_EROG,
ConCatDescr,
ConCatDescr2,
#DESCR,
Key,
if(PAZIENTE=Previous(PAZIENTE)
,if(index(Previous(ConCatDescr2),ConCatDescr)=0
,#DESCR,0)
,0) as #TherapyChanges
;
Load
PAZIENTE,
FA_DATA_EROG,
Key,
ConCatDescr,
#DESCR,
if(PAZIENTE=Previous(PAZIENTE)
,if(index(peek(ConCatDescr2),ConCatDescr)=0
,peek(ConCatDescr2)&','&ConCatDescr
,peek(ConCatDescr2))
,ConCatDescr) as ConCatDescr2
Resident TMP
Order By PAZIENTE,FA_DATA_EROG;
Drop Table TMP;

NoConcatenate
Result:
Load
Key,
Sum(#TherapyChanges) as #TherapyChanges
Resident ResultTMP
Where #TherapyChanges<>0
Group By Key;
Drop Table ResultTMP;

[Brett_Bleess]

Did Jochem's last comment get you what you needed?  If so, do not forget to return to your thread to properly close it by using the Accept as Solution button on that last post of his, this is important to do on all your threads as this is how the folks that help get credit for helping out and it also confirms to the other Members that things actually worked.  If you are still working on things and need further help, please leave an update, and if you did something different, you can post that and then use the button to mark that post as the solution too.

Regards,
Brett

[Saravanan_Desingh]

Please check this code. I think PATIENT2 in your example is wrong.

tab1:
LOAD PATIENT, Count(DISTINCT PHARMA_CODE)-1 As Code_Count
Group By PATIENT;
LOAD * INLINE [
    NUMMOV, PATIENT, DESCR, DATA_DEL, PHARMA_CODE
    67489, PROVA PAZIENTE1, ZELITREX*21 CPR RIV. 1000 MG, 19/12/2017, 029503024
    67490, PROVA PAZIENTE2, KIVEXA*600+300MG 30 CPR, 18/12/2018, 036644019
    67491, PROVA PAZIENTE2, ISENTRESS*400MG 60CPR, 18/12/2018, 038312017
    67492, PROVA PAZIENTE3, ISENTRESS*400MG 60CPR, 14/12/2019, 038312017
    67493, PROVA PAZIENTE1, TIVICAY*30CPR RIV 50MG, 14/01/2020, 043195015
    67494, PROVA PAZIENTE1, LAMIVUDINA MY*60CPR RIV 150MG, 14/01/2020, 040485029
    67495, PROVA PAZIENTE2, KIVEXA*600+300MG 30 CPR, 15/01/2020, 036644019
    67496, PROVA PAZIENTE2, ISENTRESS*400MG 60CPR, 16/01/2020, 038312017
    67497, PROVA PAZIENTE1, CELSENTRI*300MG 60CPR RIV, 14/02/2020, 038138083
    67498, PROVA PAZIENTE3, ISENTRESS*400MG 60CPR, 15/02/2020, 038312017
    67499, PROVA PAZIENTE2, ZELITREX*21 CPR RIV. 1000 MG, 16/02/2020, 029503024
     
];

[Saravanan_Desingh]