Hi all,,
In my project we were creating the monthly stored qvd which used to create qvd on monthly basis based on timestamp ....i.e on the basis of reload done and then we were concatenating the old data present in qvd and also overriding the qvd.
But some how i find the data is missing of some months like feb 2018 and april 2018 as reload was not done at that time i guess.
Can any one suggest me to recover data ?? Pls
Below is script?
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)];
