Script

[mahitham]

Script:

tempData:
Directory;
LOAD Date,
ID,
Name,
Status
FROM
[Add,Remove,Replace.xlsx]
(ooxml, embedded labels, table is Sheet1);

DataDate:
load distinct
Date
resident tempData;

NoConcatenate
maxDate:
load distinct
max(Date) as maxDate
resident tempData;

let vMax=peek('maxDate',0,'maxDate');
let vRow=NoOfRows('DataDate');
trace $(vMax);

drop table maxDate;

NoConcatenate
maxDate:
load Date as maxDate,
ID,
Name as maxName,
Status
resident tempData
where Date='$(vMax)';

for i=1 to $(vRow)
let vDate=peek('Date',$(i)-1,'DataDate');
trace $(vDate);
JOIN(maxDate)
Data1:
load '$(vDate)' as Date$(i),
ID,
Name as Name$(i),
Status
RESIDENT tempData
where Date= '$(vDate)'
and Date<>'$(vMax)';

tempDate:
load Date,ID,Flag,Status,if(Added=1,'Added',if(Renamed=1,'Renamed',if(Removed=1,'Removed',null())))as Measures;
load
if(isnull(Date$(i)),'$(vDate)',Date$(i)) as Date,
ID,
'$(i)' as Flag,
Status,
if(isnull(Date$(i)),1,0) as Added,
maxName,
Name$(i),
if( (isnull(maxDate)and not isnull(Date$(i))and isnull(maxName)),1,0) as Removed,
if(Name$(i)<>maxName and not isnull(maxName),1,0)as Renamed
resident maxDate;
set vMaxFlag=$(i);
next i
drop table tempData;
drop table maxDate;
exit script;

Expression:

Added:

count({<Measures={'Added'}>}if(Flag<>$(vMaxFlag),Measures))

Removed:

count({<Measures={'Removed'}>}if(Flag=$(vMaxFlag),0,Measures))

Renamed:

count({<Measures={'Renamed'}>}if(Flag=$(vMaxFlag),0,Measures))

[sunny_talwar]

Would you be able to add a sample app?

[mahitham]

Hi @sunny_talwar

Thanks for your reply. 

I have  attached the app and Excel below.

In Original data the above script is not working in all cases.

In the below sample chart also on 19/10/2019 the Added count is getting 9 instead of 8.

Could you please help me to change the above script.

Thanks in advance

[sunny_talwar]

In the previous version where we worked on this on the front end, we compared two dates when we saw the output... what two dates are you comparing here?