Transition Set Analysis with Aggr into Load Script

[emma-b324]

Hello,

I am looking to transition front end logic into the load script of my app because otherwise my charts will develop memory errors.  I think I need to use noconcatenate and a load statement but I am unsure of how to translate the following lines of logic into the load:

sum(if(When = 'Post Go Live',if(date(MonthStart(NOTI_OPEN_DT),'MMM-YYYY') = AsOfMonth, Sum(DISTINCT QTYConsumed), Unique) Group

sum({<When={'Post Go Live'}>}if([Notification Date]=AsOfMonth,Aggr(Sum(DISTINCT [QTY Consumed]), Unique)))
sum(if([Notification Date]=AsOfMonth,Aggr(Sum(DISTINCT [QTY Consumed]), Unique)))/Count({<[Install Start Date]-={'Not Started'},SMN=,[Material Description]=,[QTY Consumed]=,[Notification Number]=,Date=,[Notification Date]=,When=,[Notification Type]=,Unique=,[Install Start Date]=,Age=,[Waterfall Status]=>}DISTINCT [Serial Number])

sum({<When={'Pre Go Live'}>}if([Notification Date]=AsOfMonth,Aggr(Sum(DISTINCT [QTY Consumed]), Unique)))/Count({<[Install Start Date]-={'Not Started'},SMN=,[Material Description]=,[QTY Consumed]=,[Notification Number]=,Date=,[Notification Date]=,When=,[Notification Type]=,Unique=,[Install Start Date]=,Age=,[Waterfall Status]=>}Distinct if([Go Live Date]>=AsOfMonth,[Serial Number]))//Count(distinct [Serial Number])

sum({<When={'Post Go Live'}>}if([Notification Date]=AsOfMonth,Aggr(Sum(DISTINCT [QTY Consumed]), Unique)))/Count({<[Install Start Date]-={'Not Started'},SMN=,[Material Description]=,[QTY Consumed]=,[Notification Number]=,Date=,[Notification Date]=,When=,[Notification Type]=,Unique=,[Install Start Date]=,Age=,[Waterfall Status]=>}Distinct if([Go Live Date]<=AsOfMonth,[Serial Number]))

And many other statements that are like these. Since there are so many elements, I am not sure where to start. I have seen other solved queries from people in the community but I am unsure of if that will work with Aggr() and set analysis in tandem.

[stevedark]

Hi @emma-b324 

Without getting too specific about your entire syntax, I think what you are looking for is a GROUP BY statement on a RESIDENT load.

So, load all you have already and then you can issue a statement like this to give a table keyed on the Unique key that has various counts based on conditions:

UniqueAggregations:
LOAD
   Unique,
   count(DISTINCT [Serial Number]) as TotalSerialNumbers,
   count(DISTINCT if([Go Live Date]>=AsOfMonth,[Serial Number])) as SerialNosAfterGoLive,
   ... other calcs in here ...
RESIDENT ExistingTableName
GROUP BY Unique;

You seem to have things aggregated to different levels, so you will need different GROUP BY loads for each of those.

Calculations of this kind do rely on everything being on the same row in the source table, you may need to look at LEFT JOINs to bring the tables together before the GROUP BY.

Hopefully this gives you enough to point you in the right direction.

Steve

[emma-b324]

@stevedark Thank you, I will take this into consideration. I appreciate the help.