Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Accepted Solutions
Below is some sample code to show you how to handle this kind of thing. Following the sample code are images of what the data table and the aggregates table end up looking like based on this dummy data.
1. The first thing it does is force in some dummy data. I like to use the Inline statement to hand key data like this so anyone can then copy/paste as a sample to play with. You would obviously be loading from excel table or sql or wherever your real data comes from.
2. I do something in my loading called a pre-ceding load which you can search for to understand more, but it basically says load the data from the bottom first and then do something with the values. The If expression is something that would compare your value field to some hard coded number and then create another field that can be referred to later.
3. The last step I do is create another table that is based on the DataTable (Resident Load.) Notice that it does a Group By so that aggregates are prepared for each customer. The expressions are pretty simply Count and Sum. I count how many purchases there were for each customer. I Sum up the OverAmount field. Finally I demonstrate some sample of how to do a point system. The Points system could just as easily be done in the data load just like I did for the OverAmount field. You could simply say "If (Value >= 150, 125, 100) as PointsForThisPurchase" Thus the field would be 100 points for the purchase, plus additional 25 if it was over your value. Note: IF statements can be nested as well so if you give 25 for >= 150 you could then do another if instead of just using 100 points, and say 10 points if the value is greater than 110 etc.
DataTable:
// This is a preceding load and logically follows the load below it
// so first I create the dummy data, then I have some logic to check the value
// you could do this IF statement easily in your actual LOAD script from your XLS table, SQL table etc.
Load Customer, PurchaseDate, Value,
If (Value >= 150, 1, 0) as OverAmount;
LOAD * Inline [
Customer, PurchaseDate, Value
1, '20220913', 150
1, '20221101', 100
2, '20220118', 200
3, '20220920', 160
4, '20220819', 100
4, '20220917', 80
];
// This simply creates an aggregate table showing you the Expressions you could use
// notice that I use a Group By clause so I only get 1 row per customer
AggregatedData:
Load Customer,
Count(PurchaseDate) as NumberOfPurchases,
Sum(OverAmount) as NumberOfPurchasesOverAmount,
Count(PurchaseDate) * 100 + Sum(OverAmount) * 25 as PointsForPurchases
Resident DataTable
Group By Customer;
Below is some sample code to show you how to handle this kind of thing. Following the sample code are images of what the data table and the aggregates table end up looking like based on this dummy data.
1. The first thing it does is force in some dummy data. I like to use the Inline statement to hand key data like this so anyone can then copy/paste as a sample to play with. You would obviously be loading from excel table or sql or wherever your real data comes from.
2. I do something in my loading called a pre-ceding load which you can search for to understand more, but it basically says load the data from the bottom first and then do something with the values. The If expression is something that would compare your value field to some hard coded number and then create another field that can be referred to later.
3. The last step I do is create another table that is based on the DataTable (Resident Load.) Notice that it does a Group By so that aggregates are prepared for each customer. The expressions are pretty simply Count and Sum. I count how many purchases there were for each customer. I Sum up the OverAmount field. Finally I demonstrate some sample of how to do a point system. The Points system could just as easily be done in the data load just like I did for the OverAmount field. You could simply say "If (Value >= 150, 125, 100) as PointsForThisPurchase" Thus the field would be 100 points for the purchase, plus additional 25 if it was over your value. Note: IF statements can be nested as well so if you give 25 for >= 150 you could then do another if instead of just using 100 points, and say 10 points if the value is greater than 110 etc.
DataTable:
// This is a preceding load and logically follows the load below it
// so first I create the dummy data, then I have some logic to check the value
// you could do this IF statement easily in your actual LOAD script from your XLS table, SQL table etc.
Load Customer, PurchaseDate, Value,
If (Value >= 150, 1, 0) as OverAmount;
LOAD * Inline [
Customer, PurchaseDate, Value
1, '20220913', 150
1, '20221101', 100
2, '20220118', 200
3, '20220920', 160
4, '20220819', 100
4, '20220917', 80
];
// This simply creates an aggregate table showing you the Expressions you could use
// notice that I use a Group By clause so I only get 1 row per customer
AggregatedData:
Load Customer,
Count(PurchaseDate) as NumberOfPurchases,
Sum(OverAmount) as NumberOfPurchasesOverAmount,
Count(PurchaseDate) * 100 + Sum(OverAmount) * 25 as PointsForPurchases
Resident DataTable
Group By Customer;
