topic Re: Aggregation / Counting in a script in QlikView

Aggregation / Counting in a script

anthony_kinsell — Thu, 02 Jan 2020 10:06:28 GMT

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,

Re: Aggregation / Counting in a script

zhadrakas — Thu, 02 Jan 2020 10:19:51 GMT

maybe this

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

Re: Aggregation / Counting in a script

anthony_kinsell — Thu, 02 Jan 2020 10:24:36 GMT

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

Re: Aggregation / Counting in a script

zhadrakas — Thu, 02 Jan 2020 10:29:44 GMT

this should work.

Can you share your complete Load Statement?

- Maybe you are missing the group by statement?

Regards

Re: Aggregation / Counting in a script

anthony_kinsell — Thu, 02 Jan 2020 10:34:21 GMT

// 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';

Re: Aggregation / Counting in a script

zhadrakas — Thu, 02 Jan 2020 10:43:01 GMT

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

Re: Aggregation / Counting in a script

anthony_kinsell — Thu, 02 Jan 2020 11:59:57 GMT

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.

Re: Aggregation / Counting in a script

zhadrakas — Thu, 02 Jan 2020 12:03:19 GMT

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

Re: Aggregation / Counting in a script

anthony_kinsell — Thu, 02 Jan 2020 13:02:56 GMT

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.

Re: Aggregation / Counting in a script

zhadrakas — Thu, 02 Jan 2020 13:05:41 GMT

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

Re: Aggregation / Counting in a script

anthony_kinsell — Thu, 02 Jan 2020 15:26:27 GMT

Thanks Tim,

Really appreciate the help - almost there I think - it seems the formula isn't counting the distinct TenantRefs though - seems to be counting them all.

I've tried to attach a snip from a straight table of the data - all figures should be 1 - as it's counting the distinct value - however you'll see that in this case the highlighted line is 3. The invoice was entered incorrectly initially, this was credited off & then reentered correctly - all under the same tenant so value should be 1.

Re: Aggregation / Counting in a script

Brett_Bleess — Thu, 02 Jan 2020 15:43:17 GMT

Anthony, Tim is likely the better resource here, but I think you need some logic to check if there are multiple transactions in those cases and only load the line you want... Not sure if that will make sense or not, the Peek() function would be part of that solution I think, but I am not a very good developer just FYI. The only other thing I can offer is the following link to the Design Blog area of the Community, there are hundreds of mostly how-to topics that may be of some help.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

I am not exactly sure what you need at this point, otherwise I would have done some searching and give you something more specific from there, felt it was better to let you poke around on your own in this case, so I do not confuse you! 🙂 Hopefully this will help, but maybe Tim will be back with something more helpful or someone else will chime in on things.

Regards,
Brett

Re: Aggregation / Counting in a script

anthony_kinsell — Thu, 02 Jan 2020 15:47:40 GMT

Thanks Brett,

I thought by putting DISTINCT into the formula this was saying only count the DISTINCT TenantRefs - that's how it works in a formula in a chart - may be different in script.

So for example if I have a TenantRef of HAN01/01/01 appear 3 times in 1 month (like in the example - an invoice entered incorrectly - credited & entered correctly) - then it only counts as 1 not 3 - that's what I'm trying to achieve.

Anthony

Re: Aggregation / Counting in a script

Brett_Bleess — Thu, 02 Jan 2020 15:59:49 GMT

Anthony, yeah, sorry, you are correct there, but there must be something in the data model that is causing things to think those are all unique, i.e. something in the 'full' record that makes each one unique, in that case, I think the distinct would do what it is doing in your case, but I could be wrong, again, I am not too swift on the development side, I know enough to be pretty dangerous, hopefully someone else with better dev skills can have a look and see if they concur etc.

Cheers,
Brett

Re: Aggregation / Counting in a script

Arthur_Fong — Fri, 03 Jan 2020 02:36:15 GMT

Your script is reading:

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

Is <HAN02/01/70> (credited off & then reentered correctly) a member from Account 6101?

Re: Aggregation / Counting in a script

anthony_kinsell — Fri, 03 Jan 2020 08:32:04 GMT

Hi Arthur,

Yes all 3 entries are members of 6101

Re: Aggregation / Counting in a script

Arthur_Fong — Fri, 03 Jan 2020 08:44:35 GMT

Is that column an expression like:

sum(UnitSales)?

Maybe try:

sum(aggr(count(distinct UnitSales),<dim1>,<dim2>,<dim3>))

Re: Aggregation / Counting in a script

anthony_kinsell — Mon, 06 Jan 2020 14:30:55 GMT

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