Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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))
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?
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.
Are you working on QS or QV environment?
I did this in QV, but i see your screenshot is in QS environment.
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
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))
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