Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
anthony_kinsell
Creator
Creator

Aggregation / Counting in a script

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,

 

1 Solution

Accepted Solutions
anthony_kinsell
Creator
Creator
Author

Hi Tim,

I solved this by creating a new source to count the unit sales - basically as you suggested above from the IncomeTemp table.

I then concatenated this into my fact table - worked perfectly.

Thanks again for your assistance

View solution in original post

17 Replies
zhadrakas
Specialist II
Specialist II

maybe this

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

 

anthony_kinsell
Creator
Creator
Author

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

zhadrakas
Specialist II
Specialist II

this should work.

Can you share your complete Load Statement?

- Maybe you are missing the group by statement?

Regards

 

anthony_kinsell
Creator
Creator
Author

// 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
Specialist II
Specialist II

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
Creator
Creator
Author

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
Specialist II
Specialist II

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
Creator
Creator
Author

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
Specialist II
Specialist II

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