topic Re: Need data just from MonthEnd in Connectivity & Data Prep

Need data just from MonthEnd

Thu, 28 Sep 2017 21:03:58 GMT

Hi,

I have a table that lists a number ("OCCUPUNITNO") for every day but need to do a line chart that just shows the "OCCUPUNITNO" numeric for the last day of every month.

Current Dimension =MonthEnd(ActivityDate)

Measure = Broken:(

I can get an average for the month with AVG(OCCUPUNITNO) but just cannot get the data from one day.

Any help greatly appreciated please. I am sure this is simple and I am being dumb.

Re: Need data just from MonthEnd

eduardo_dimperio — Thu, 28 Sep 2017 21:14:10 GMT

Can you put in your script this:

AUX:

LOAD

OCCUPUNITNO,

Month(ActivityDate) AS MONTH,

Day(ActivityDate) AS DAY

FROM YOURQVD

FINAL:

LOAD

MAX(DAY),

MONTH,

OCCUPUNITNO

RESIDENT AUX

GROUP BY

MONTH,

OCCUPUNITNO

;

Drop Table AUX;

This will show only the last day of the month where OCCUPUNITNO occur, if i right understant your need

Re: Need data just from MonthEnd

Thu, 28 Sep 2017 21:19:24 GMT

Hi,

Thanks for your response. I get the following error.

The error occurred here:

AUX: LOAD OCCUPUNITNO, Month(ActivityDate) AS MONTH, Day(ActivityDate) AS DAY FROM YOURQVD FINAL: LOAD MAX(>>>>>>DAY<<<<<<), MONTH, OCCUPUNITNO RESIDENT AUX GROUP BY MONTH, OCCUPUNITNO

Re: Need data just from MonthEnd

eduardo_dimperio — Thu, 28 Sep 2017 21:42:19 GMT

Ah, sorry i think that in english DAY is a reserved word.

Try this

AUX:

LOAD

OCCUPUNITNO,

Month(ActivityDate) AS SELECT_MONTH,

Day(ActivityDate) AS SELECT_DAY

FROM YOURQVD

FINAL:

LOAD

MAX(SELECT_DAY),

SELECT_MONTH,

OCCUPUNITNO

RESIDENT AUX

GROUP BY

SELECT_MONTH,

OCCUPUNITNO

RESIDENT AUX;

Drop Table AUX;

Re: Need data just from MonthEnd

Thu, 28 Sep 2017 21:59:39 GMT

Sorry still luck.

The following error occurred:

Unexpected token: 'SELECT_DAY', expected one of: 'symbol', 'biff', 'dif', 'fix', 'html', 'json', 'kml', ...

The error occurred here:

AUX: LOAD OCCUPUNITNO, Month(ActivityDate) AS SELECT_MONTH, Day(ActivityDate) AS SELECT_DAY FROM YOURQVD FINAL: LOAD MAX(>>>>>>SELECT_DAY<<<<<<), SELECT_MONTH, OCCUPUNITNO RESIDENT AUX GROUP BY SELECT_MONTH, OCCUPUNITNO

Re: Need data just from MonthEnd

Thu, 28 Sep 2017 22:00:00 GMT

Do I need to change FROM YOURQVD to something else?

Re: Need data just from MonthEnd

eduardo_dimperio — Thu, 28 Sep 2017 22:12:50 GMT

Yeah!

YOURQVD means the address where your file are, like C:\\YOURQVDNAME, this could even not be a qvd but a excel file or a select in a DB.

Re: Need data just from MonthEnd

Fri, 29 Sep 2017 01:56:26 GMT

OK I need to connect it to my DB. So what would I use if current SQL looks like...

LIB CONNECT TO 'SSHOF';

SQL SELECT ActivityDate,

ActivityWeek,

ActivityMonth,

SortReport,

CalendarNotes,

CalcErrors,

LastUpdate,

LastUpTime,

DiscUnitNo,

DiscArea,

FacilityCode,

AvgStay,

InquiriesTaken,

Conversions,

ConvertTime,

ResvtnsTaken,

ResvtnsCancel,

MoveIns,

MoveOuts,

TotUnitNo,

OccupUnitNo,

VacantUnitNo,

OtherUnitNo,

TotUnitArea,

OccupArea,

VacantArea,

OtherArea,

TotValue,

OccupValue,

OccupValActual,

VacantValue,

OtherValue,

RentChg,

RentRec,

DepositChg,

DepositRec,

LateFeeChg,

LateFeeRec,

InsureChg,

InsureRec,

MerchChg,

MerchRec,

OtherChg,

OtherRec,

TaxChg,

TaxRec,

LateFeeWaived,

ActiveAgreemts,

InsureAgreemts,

InsureNew,

InsureCancel,

Arrears1No,

Arrears2No,

Arrears3No,

Arrears4No,

AdvanceBillNo,

AdvancePaidNo,

Arrears1Value,

Arrears2Value,

Arrears3Value,

Arrears4Value,

AdvBillRntValue,

AdvancePaidValue,

DepositsHeld,

CashRec,

CreditRec,

Refunds,

DiscChg,

DiscRec,

MktgEmailSent,

MktgLetterSent,

InsureUnits,

InsureValue,

ArrearsInactiveNo,

ArrearsInactiveValue,

AdvanceBillOtherValue,

Checksum,

MerchReturnChg,

MerchReturnRec,

BadDebtChg,

BadDebtRec,

AdvBillInsValue,

AdvBillLateValue

FROM Activity WHERE ActivityDate > '31/12/14' AND ActivityDate < '$(vToday)' AND FacilityCode LIKE 'SSP%' OR FacilityCode LIKE 'SSWSG%';