Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
Would you be able to add a sample app?
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
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?