Hello there!
I'm working on a load script to calculate the monthly stats based on two simple data tables - Devices and Transactions.
The simplified Devices structure is as follows:
- %DeviceID
- Installation date
The simplified Transactions structure is as follows:
- %TransactionID
- Subscription Start Date
I've also created a MasterCalendar table to map those tables together with the following structure:
MasterCalendar:
LOAD
TempDate AS [Date],
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Date(MonthStart(TempDate), 'YYYY-MM') AS [Year/Month]
RESIDENT TempCalendar;
My goal is to create a new table MonthlyStats with the following structure:
- Year/Month - each month
- DeviceCount - number of new device installs in this month
- TransactionCount - number of started transactions in this month
I've tried to manage it with the following load script:
// Step 1: Load the MonthlyStatsTemp
MonthlyStatsTemp:
LOAD
[Year/Month]
RESIDENT MasterCalendar;
// Step 2: Aggregate and count records in Devices by Year/Month
DevicesCounts:
LOAD
Date(MonthStart([Installation date]), 'YYYY-MM') AS [Year/Month], // Convert to Year/Month format
Count([%DeviceID]) AS DeviceCount // Count the number of devices for each month
RESIDENT [Devices]
GROUP BY
Date(MonthStart([Installation date]), 'YYYY-MM');
// Step 3: Aggregate and count records in Transactions by Year/Month
TransactionsCounts:
LOAD
Date(MonthStart([Subscription Start Date]), 'YYYY-MM') AS [Year/Month], // Convert to Year/Month format
Count([%TransactionID]) AS TransactionCount // Count the number of transactions for each month
RESIDENT [Transactions]
GROUP BY
Date(MonthStart([Subscription Start Date]), 'YYYY-MM');
// Step 4: Left Join the counts with the MonthlyStatsTemp
Left Join (MonthlyStatsTemp)
LOAD
[Year/Month],
DeviceCount
RESIDENT DevicesCounts;
Left Join (MonthlyStatsTemp)
LOAD
[Year/Month],
TransactionCount
RESIDENT TransactionsCounts;
// Step 5: Create the MonthlyStats table
MonthlyStats:
LOAD
[Year/Month],
If(IsNull(DeviceCount), 0, DeviceCount) AS DeviceCount, // Replace nulls with 0
If(IsNull(TransactionCount), 0, TransactionCount) AS TransactionCount // Replace nulls with 0
RESIDENT MonthlyStatsTemp;
// Drop the temporary tables
DROP TABLE DevicesCounts;
DROP TABLE TransactionsCounts;
DROP TABLE MonthlyStatsTemp;
But the final table MonthlyStats wasn't created at all.
Could you help me find out what I'm doing wrong, please? How shall I fix it to make it work?
Many thanks!
