Script Help

[mahitham]

Hi,

Can any one please help me on below requirement.

Please find the sample data Excel source file. Here From Date need to Derive a Comparision Date like below.

[Main Table]:

LOAD
   "Date"          as   "Report Date",  
    ID,
    Name,
    Status
FROM [lib://Connection/Add,Remove,Replace.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Alt Table]:
Load
    "Report Date"    as   "Comparision Date"
Resident [Main Table];

Report Date is always Max Date. Need to compare the Max Report Date 20-10-2019 with Comparison Dates and need to create a below trend chart to find out how many ids added,Removed and Deleted.

Tried below Expression for 

Added:

=Sum(Aggr(If(Count(ID) = 1 and Count(DISTINCT {<[Report Date]>} If([Report Date] = [Comparision Date], ID)) = 0, 1, 0), [Comparision Date], ID))

Removed:

=Sum({<[Report Date]>} Aggr(If(Count(ID) = 0 and Count(DISTINCT {<[Report Date]>} If([Report Date] = [Comparision Date], ID)) = 1, 1, 0), [Comparision Date], ID))

Renamed:

=Sum(Aggr(
 If(Count(ID) = 1 and
       Count(DISTINCT {<[Report Date]>} If([Report Date] = [Comparision Date], ID)) = 1 and
       Only(Name) <> Only({<[Report Date]>} If([Report Date] = [Comparision Date], Name))
    , 1, 0)
, [Comparision Date], ID))

But these expressions were not working on large data sets taking lot of time to display the data in Chart due aggr function.

Is there any way to simplify the above expressions or is there any backend solution please help.

Thanks in advance

[Arthur_Fong]

1.  How do you identify which ID is added, removed or renamed?

The Status is only Pass or Fail in your attached sample.

Say for example ID 1 on 11/10/2019, it should be a new ID added into the system.

Now come to 15/10/2019, ID 1 is in, let's assume this is for renaming purposes.

Then in 20/10/2019, ID 1 is in, and is this for removal purposes?  

2. How do you get 6/1/1 on 11/10/2019?

I see 4 passes and 1 failed on 11/10/2019, in total 5 record. But how do you get the total record of 6+1+1 =8?

[mahitham]

Hi @Arthur_Fong 

It's not based on Status its based on ID.

Added:

Comparison Date 11-10-2019 is compared with Report Date is always Max Date i.e., 20-10-2019 

So IDs 6,7,8,9,10,11 are added So count on Trend for Added is 6.

same like for Comparison Date 15-10-2019 compared with Max Date i.e., 20-10-2019 

So IDs 8,9,10,11 added So count on Trend for Added is 4.

Removed :

Comparison Date 11-10-2019 is compared with Report Date is always Max Date i.e., 20-10-2019 here

ID=3 is removed so count on Trend for Removed is 1.

Renamed:

Comparison Date 11-10-2019 is compared with Report Date is always Max Date i.e., 20-10-2019 here

for ID=5,Name=E that's renamed as Name =M for ID=5 in 20-10-2019

so count on Trend for Renamed is 1.

[Arthur_Fong]

Are you working on QS or QV environment?

[Arthur_Fong]

I did this in QV, but i see your screenshot is in QS environment.

[mahitham]

Hi @Arthur_Fong 

Thanks for your reply.

I did in Qliksense.

Could you please add the script and Expressions here.I don't have QlikView license.

Thanks in advance

[Arthur_Fong · Accepted solution]

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

[Brett_Bleess]

Mahitha, if Arthur's posts have gotten your solution working, please be sure to come back to this thread to use the Accept as Solution button on the post(s) of his that helped you get things working, this gives him credit and lets others know what worked.

Regards,
Brett