Aggregation / Counting in a script

[anthony_kinsell]

Hi,

I'm having some trouble getting the script correct to create a sales number per month from my data.

Basically I need to count the distinct 'TenantRef' in the data where the 'Account' is 6101.

I know this should be simple but keep getting invalid function errors - can anyone help?

I've tried the following type of script:

If(Account = '6101', Count(DISTINCT TenantRef)) as UnitSales,

[zhadrakas]

maybe this

Count(DISTINCT If(Account = '6101',TenantRef)) as UnitSales,

[anthony_kinsell]

Thanks but unfortunately not - still coming up as 'Invalid Expression' when I reload the script

[zhadrakas]

this should work.

Can you share your complete Load Statement?

- Maybe you are missing the group by statement?

Regards

[anthony_kinsell]

// Load the income from Qube
IncomeTemp:
LOAD
Entity as OwnerRef,
ApplyMap('OwnerMap',Project) as OwnerName,
IF(Project = 'ZFAJ/STP05X', 'FAJ/STP05',
IF(Entity = 'MRC' and TenantName = 'Hansfield Investments Ltd' , 'MRC13',
IF(Entity = 'QUA' and TenantName = 'J7 Co-Ownership' , 'QUA/CN/CBLK4',
Project))) as PropertyRef,
IF(Entity = 'ADL', 'Hogan Place'
,ApplyMap('PropNameMap', Project, 'MISSING')) as Property ,
UnitRef,
FundType,
Alt_Name,
If(FundType = 'Proforma', Mid(FundDesc,10),
If(FundType = 'Proforma Constr', Mid(FundDesc,10),
If(FundType = 'Proforma Rent', Mid(FundDesc,15),
IF(Project = 'HAN01' and FundDesc = 'DO NOT USE' , 'Phase 1 (37 Units)',
IF(Project = 'HAN01' and FundDesc = 'P 1.1 +1.2 (15 units)' , 'Phase 1 (37 Units)',
IF(Entity = 'MRC' and TenantName = 'Hansfield Investments Ltd' , 'New Homes Overheads',
IF(Entity = 'QUA' and TenantName = 'J7 Co-Ownership', 'City North Commercial Block 4'
,FundDesc))))))) as FundDesc,
QubePhase as FundRef,
QubeCode,
QubePhase,
TenantRef,
Count(DISTINCT If(Account = '6101', TenantRef)) as UnitSales,
If(Account = '6107' and Entity = 'MGH', 'Residential Sales',
If(Account = '6107' and Entity = 'HAN', 'Residential Sales',
If(Account = '6107' and Match(Entity, 'SR5', 'AHL'), 'Land Sales',
If(Account = '6107' and Match(Project, 'QUA/CN/IND4','QUA/CN/IND3','CNT/CN/CBLK3','QUA/CN/CBLK3'), 'Commercial Sales',
If(Account = '6107' and Project = 'CNT/CN/HO', 'Land Sales',
If(Account = '6107' and Match(Project, 'APL05','CNT/CN/IND3'), 'Miscellaneous Income',
If(Project = 'MRC13' and FundHeading = 'Rent and Admin Income','Management Fee Income',
FundHeading))))))) as FundHeading,
IF(Project = 'APL03',ApplyMap('AptsTenantMap',TenantRef),
IF(Project = 'SR201',ApplyMap('AptsTenantMap',TenantRef),
IF(Project = 'SR202',ApplyMap('AptsTenantMap',TenantRef),
IF(Project = 'SR215',ApplyMap('AptsTenantMap',TenantRef)
,TenantName)))) as TenantName,
TenantType,
IF(FundHeading = '* Sales',UnitDescription,
ChargeDescription) as ChargeDescription,
Account as OrgQubeAccount,
If(Match(Account, '6107', '6101') and Match(Project, 'MRD/DUN04', 'MRD/DUN05', 'MRD/RAT03', 'MRD/DUN02', 'MRD/DUN03', 'RIM03', 'RIM04' ),'6118',
If(Account = '6107' and Entity = 'MGH','6101',
If(Account = '6107' and Entity = 'HAN','6101',
If(Account = '6107' and Match(Entity, 'SR5', 'AHL'),'6109',
If(Account = '6107' and Match(Project, 'QUA/CN/IND4','QUA/CN/IND3','CNT/CN/CBLK3','QUA/CN/CBLK3'),'6108',
If(Account = '6107' and Match(Project, 'CNT/CN/HO'),'6109',
If(Account = '6107' and Match(Project, 'APL05','CNT/CN/IND3'),'6105',
IF(IncExp = 'Income' and Entity = 'MRC' and Left(ChargeDescription,5) = 'Sales', '6119',
IF(IncExp = 'Income' and Entity = 'MRC' and Left(ChargeDescription,5) = 'Devel', '6120',
Account))))))))) as Account,
Category,
Currency,
Scenario,
TransactionID as IncAuditNum,
From,
To,
Date(Date, 'DD/MM/YY') as IncomeDate,
Date(Entrydate, 'DD/MM/YY') as IncEntryDate,
Year(Entrydate) & '-' & Month(Entrydate) as IncEntryYrMth,
Trantype,
InvNumber,
Amount as Nett,
VAT,
Gross as Amount,
Received,
IncExp,
TenantStatus,
Inerror,
Cancelled,
IF(IncExp = 'Income' and Entity = 'HAN' , (Right(TenantRef,2) & ' ' & UnitDescription),
IF(IncExp = 'Income' and Entity = 'MGH' , (Right(TenantRef,2) & ' ' & UnitDescription),
IF(IncExp = 'Income' and Project = 'RIM08' , (Right(TenantRef,2) & ' ' & UnitDescription),
IF(IncExp = 'Income' and Entity = 'MRC' and Left(ChargeDescription,5) = 'Sales', Alt_Name,
IF(IncExp = 'Income' and Entity = 'MRC' and Left(ChargeDescription,5) = 'Devel', Alt_Name,
UnitDescription))))) as UnitDescription,
ApplyMap('MGHNomPerADJMap', TransactionID, NomPeriod) as NomPeriod,
if (Match(TenantType, 'INTAM','EXTAM'), 'INV' & NUM(QubeCode),
if (Match(TenantType, 'INTCON','EXTCON'), 'CON' & NUM(QubeCode),
'DEV' & NUM(QubeCode)))
as QubeCodeMap

FROM
$(vLoadPath)\$(vIncomeFile)
(ooxml, embedded labels, table is Sheet1)

where [Trantype] <> 'Receipt' and [Trantype] <> 'Refund' and TransactionID <> '65052' and TransactionID <> '72971' and TransactionID <> '65658' and TransactionID <> '72973'
and TransactionID <> '68057' and TransactionID <> '72975' and TransactionID <> '69414' and TransactionID <> '69448' and TransactionID <> '72880' and TransactionID <> '72882';

[zhadrakas]

you are missing the "Group by" Statement at the end of your script.

I cannot recommend doing it here cause you need to group the statement by every non aggregated column. 

Instead you think about Creating another aggregated table like

IncomeAgg:
Load OwnerRef,
     OwnerName,
     Count(DISTINCT If(Account = '6101', TenantRef)) as UnitSales
Resident IncomeTemp
Group by OwnerRef, OwnerName
;
//add More dimensions if you need to

[anthony_kinsell]

I've set it up as a separate table & concatenated it with my FACT table.

It has worked in so far as there is now a populated UnitSales field, but it doesn't seem to have taken the If(Account ='6101') part into account - i.e. it has populated for data outside this code also.

Not the end of the world - I can use set analysis to limit this in my chart.

[zhadrakas]

concatenation seems wrong to me here.

i would do it like this:

inner join (IncomeTemp)
Load OwnerRef,
     OwnerName,
     Count(DISTINCT If(Account = '6101', TenantRef)) as UnitSales
Resident IncomeTemp
Group by OwnerRef, OwnerName
;
//add More dimensions if you need to

[anthony_kinsell]

Apologies for my ignorance but not sure how this will work.

For further context I have 4 sources of data, Income, Expenditure, Stock & Budget which all concatenate into a fact table within my script. Hence I was trying to create a 5th source above.

[zhadrakas]

Then i can only recommend you taking the first approach where the table stays alone as an aggregated table linked over (in best case) one dimension from your fact table.

regards

tim