Store Historical QVD

[sona_sa]

Hi

I am spiliting my Historical QVD, It is working fine, But need some changes - I want, If the QVD is Current Month Data then if should to store as E2E_Historization, Else If Qvd is Previous Month the it should to store as Mar 2018_E2E_Historization, Apr 2018_E2E_Historization, May 2018_E2E_Historization...... Below is my code.

E2E_Historization_Date:

LOAD Type,

     DATE(SnapshotDate,'DD-MMM-YYYY') AS SnapshotDate,

     LinkKey,

     Status,

     Count,

     Owner,

     XFT,

     LifeCycle,

     Applicability,

     Activities

FROM

(qvd);

Month:

Load Distinct Monthname(SnapshotDate) as Month

Resident E2E_Historization_Date;

let vCount = NoOfRows('Month');

For i=0 to $(vCount) -1

Let vMonth = Peek('Month',$(i),'Month');

[$(vMonth)]:

Load *,'$(vMonth)' as Month1 Resident E2E_Historization_Date where MonthName(SnapshotDate) = '$(vMonth)';

If (Num(Max(Month(SnapshotDate))) = Num(Month(Today()))) Then

Store [$(vMonth)] into [E2E_Historization].qvd;

ELSEIF

Store [$(vMonth)] into [$(vMonth)_E2E_Historization].qvd;

ENDIF

Drop table [$(vMonth)];

NEXT

Drop table E2E_Historization_Date;

[sunny_talwar · Accepted solution]

May be try this

Month:

Load Distinct Monthname(SnapshotDate) as Month,

     Num(MonthStart(SnapshotDate) as MonthNum

Resident E2E_Historization_Date;

LET vCount = NoOfRows('Month');

FOR i=0 to $(vCount) -1

     Let vMonth = Peek('Month',$(i),'Month');
     Let vMonthNum = Peek('MonthNum',$(i),'Month');

     [$(vMonth)]:

     Load *,

          '$(vMonth)' as Month1

     Resident E2E_Historization_Date

     Where MonthName(SnapshotDate) = '$(vMonth)';

     If $(vMonthNum) = Num(MonthStart(Today())) Then

          Store [$(vMonth)] into [E2E_Historization].qvd;

     ELSEIF

          Store [$(vMonth)] into [$(vMonth)_E2E_Historization].qvd;

     ENDIF

     DROP Table [$(vMonth)];

NEXT

DROP Table E2E_Historization_Date;