Hi all,
I am creating the monthly qvd using following script.But some i am missing data of some months ...like 2018-02 and 2018-11.
//Calculate current month Situation
Trending:
LOAD Distinct
text(Timestamp(today(),'YYYY-MM')) as ReportMonth,
date(Timestamp(today(),'YYYY-MM')) as Report_Month,
ContractShipToCountry,
Portfolio,
ContractStatus,
Count(distinct Contract_Equipment_Id) as NonSMAEligibleScope,
Count(distinct if(not isnull(DQ_NonSMAProduct),Contract_Equipment_Id,null())) as NonSMAEligible,
Count(distinct if(SLCPType='SLCP' and isnull(DQ_NonSMAProduct) and not isnull(DQ_MissingSWSerial),Contract_Equipment_Id,null())) as MissingSWSerial,
Count(distinct if(SLCPType='SLCP' and isnull(DQ_NonSMAProduct),Contract_Equipment_Id,null())) as MissingSWSerialScope,
Count(distinct if(ApplyMap('MAPPICProducts',CommercialMaterial,'N')='N' and isnull(DQ_NonSMAProduct),Contract_Equipment_Id,null())) as MissingCounterScope,
Count(distinct if(ApplyMap('MAPPICProducts',CommercialMaterial,'N')='N'
and isnull(DQ_NonSMAProduct) and not isnull(DQ_MissingCounter),Contract_Equipment_Id,null())) as MissingCounter,
Count(Distinct if(EOLReached='Y' and isnull(DQ_NonSMAProduct),Contract_Equipment_Id,null())) as EOLEquipment,
Count(distinct if(isnull(DQ_NonSMAProduct),Contract_Equipment_Id,null())) as EOLEquipmentScope
RESIDENT SMA
WHERE (index('Active,Pending',ContractStatus)>0)
GROUP BY Timestamp(today(),'YYYY-MM'),ContractShipToCountry, Portfolio,ContractStatus;
LET vCount = NoOfRows('Trending');
LOAD Today() as Date, $(vCount) as RecordCount AutoGenerate 1 ;
// Load measures history from QVD for previous Months only
SET QVDFile='..\5_QVD\DQ_Trending.qvd';
IF NOT(isnull(QvdCreateTime('$(QVDFile)'))) then
Trace Loading metrics trending from QVD...;
Concatenate (Trending)
LOAD *
// ReportMonth,
// SoldToCountry as ContractShipToCountry,
// 'PCMS Portfolio' as Trend_Portfolio,
// NonSMAEligibleScope,
// NonSMAEligible,
// MissingSWSerial,
// MissingSWSerialScope,
// MissingCounterScope,
// MissingCounter,
// EOLEquipment,
// EOLEquipmentScope
FROM [$(QVDFile)](qvd)
WHERE ReportMonth < text(Timestamp(today(),'YYYY-MM'));
END IF
//Store trending in QVD (including today's values) for next reload
TRACE Saving updated Trending to QVD...;
STORE Trending INTO [$(QVDFile)];
Can any one help me in this.
Kind Regards
Manu
