Script

mahitham — Wed, 20 Nov 2019 07:51:25 GMT

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))

Re: Script

sunny_talwar — Wed, 20 Nov 2019 12:13:09 GMT

Would you be able to add a sample app?

Re: Script

mahitham — Wed, 20 Nov 2019 13:33:20 GMT

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

Re: Script

sunny_talwar — Wed, 04 Dec 2019 19:36:31 GMT

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?

topic Re: Script in QlikView

Script

Re: Script

Re: Script

Re: Script