Load last 6 quarters from qvd

[Anonymous]

Here is my code below. the code is at the data model level. i created the qvd using a QVD generator.

Currently the files in the qvd are for: 2016Q2, 2016Q3, 2016Q4, 2017Q1, 2017Q2, 2017Q3, 2017Q4

I want to load only the the last 6 quarters from the qvd - 2016Q3 till 2017Q4

I will be getting a new file every quarter, so next file will be 2018Q1 and that will be the max quarter

This is to be done in script as it will be binary loaded into another app

CQuarter stores  the quarters in text format

CS:

LOAD

     CVen,

     CPerf,

     CCon,

     CRelationship,

     CRisk,

     COver,

     CQuarter,

     Dual(CQuarter, QuarterStart(Makedate(Left(CQuarter,4)),Right(CQuarter,1)-1)) as CQuarterDual

FROM

$(vQvdPath)M_CS.qvd(qvd);

Left Join(CS)

load

max(CQuarterDual) as maxCQuarter

resident Customer_Satisfaction;

Let vVMO_Current_Quarter = peek('maxCQuarter',0,'Customer_Satisfaction');

Let vVMO_Latest_CS_Date = num($(vVMO_Current_Quarter),6);

Let vVMO_History_Quarter_Limit=num(quarterstart(date($(vVMO_Current_Quarter),'MM/DD/YYYY'),-5));

[sunny_talwar]

May be like this

CS:

LOAD

     CVen,

     CPerf,

     CCon,

     CRelationship,

     CRisk,

     COver,

     CQuarter,

     Dual(CQuarter, QuarterStart(Makedate(Left(CQuarter,4)),Right(CQuarter,1)-1)) as CQuarterDual

FROM

$(vQvdPath)M_CS.qvd(qvd);

Left Join(CS)

LOAD Max(CQuarterDual, 6) as IncludeQuarter

resident Customer_Satisfaction;

FinalCS:

NoConcatenate

LOAD *

Resident CS

Where CQuarterDual >= IncludeQuarter;

DROP Table CS;

[Anonymous]

There was a synthetic key produced for which I did:

FinalCS:

NoConcatenate

LOAD

CVen as CV,

     CPerf as CP,

     CCon as CC,

     CRelationship as CRe,

     CRisk as CRi,

     COver as CO,

     CQuarter as CQ,

     CQuarterDual as CQD

Resident Customer_Satisfaction

Where CQuarterDual >= maxCQuarterSix;

Do I have to use alias or is there another way i can use the original fields. If I have to use alias, then I have to change the fields in the main app which is getting binary loaded from this file

Also, when I commented the 'No Concatenate', it did not make any changes except for the sort order. Is 'No Concatenate' necessary.

[Anonymous]

I forgot to add DROP Table CS; in the script. It works now. Thanks for the help