Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In my application I have a table CLAIMFACTS, about 55,000 rows
To make statistics I aggregate this set of data with:
RolledUpChassisCost:
LOAD
Chassis,
ClaimYear,
Count(DISTINCT Chassis) as RolledUpChassisCount,
Sum([DAF Claim- Total]) as RolledUpChassisCost,
Floor(Sum([DAF Claim- Total])/100) * 100 as RolledUpCostBucket
Resident ClaimFacts
Group By Chassis, ClaimYear;
This works fine, and if I pivot this in Excel around the RolledUpCostBucket dimension I get a nice frequency histogram. Chassis frequency is in hundreds, which is correct.
Then I try the pivot in next part of my script:
Buckets:
LOAD
ClaimYear,
Sum(RolledUpChassisCost)/Sum(RolledUpChassisCount) as AverageYearlyCost,
Floor((Sum(RolledUpChassisCost)/Sum(RolledUpChassisCount))/100)*100 as AverageYearlyBucket,
Count(DISTINCT Chassis) as AverageYearlyFreq
Resident RolledUpChassisCost
Group By ClaimYear;
However, AverageYearlyFreq is now in tens of thousands (should be in hundreds), two developers cannot solve this.
Sorry, I cannot upload qvw due to policy.
Marty.
now both the tables are linked based on the ClaimYear field.
In table one you do not have distinct ClaimYear as it was group by along with Chassis.
now both the tables are linked based on the ClaimYear field.
In table one you do not have distinct ClaimYear as it was group by along with Chassis.
Hey Martyn,
Would it be possible to attach an excel file with some dummy data and maybe whats the result you are getting vs expected. That would greatly help in addressing the issue.
Just an FYI,
Count(DISTINCT Chassis) as RolledUpChassisCount,
The above line in the first table will always be 1 as you are grouping by Chassis as well. So not sure why you are using that. Its equivalent to
1 as RolledUpChassisCount
Thanks
AJ
Thanks, I didn't intend to link the tables.