topic Re: Load last 6 quarters from qvd in QlikView

Load last 6 quarters from qvd

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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));

Re: Load last 6 quarters from qvd

sunny_talwar — Wed, 07 Feb 2018 20:11:47 GMT

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;

Re: Load last 6 quarters from qvd

Anonymous — Wed, 07 Feb 2018 20:55:20 GMT

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.

Re: Load last 6 quarters from qvd

Anonymous — Wed, 07 Feb 2018 21:16:06 GMT

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