topic Suggestions for reducing rows in order to show Opening and Closing base in App Development

Suggestions for reducing rows in order to show Opening and Closing base

sandeepgather — Wed, 18 May 2022 04:36:28 GMT

We have an App which contains around 200M rows. It represents total number of customers we have at the end of each month. We need to create a view showing Opening Count, movements within the month like addition deletion and then Closing count for each month

	March	April
Opening	4000	3900
Addition	500	300
Deletion	600	100
Closing	3900	4100

Problem - We are currently duplicating the numbers of rows by loading the same Closing month file and increasing the date by 1 to make it as Opening count for next month. Is their any smarter way of implementing so that we can reduce the number of rows?

Re: Suggestions for reducing rows in order to show Opening and Closing base

vinieme12 — Wed, 18 May 2022 05:50:14 GMT

There's absolutely no need to duplicate the data

Just create a date bridge to link the monthend date to also associate is a opening date for next month

In charts refer to the Datetype field when doing calculations

Opening: count({<DateType={'Opening'}>}CustomerID)

Closing : count({<DateType={'Closing'}>}CustomerID)

Difference: count({<DateType={'Opening'}>}CustomerID)-count({<DateType={'Closing'}>}CustomerID)

raw:
Load Date(MonthEnd) as MonthEnd,CustomerID Inline [
MonthEnd,CustomerID
2022-03-31,1
2022-03-31,2
2022-03-31,3
2022-03-31,4
2022-04-30,1
2022-04-30,2
2022-04-30,3
2022-04-30,4
2022-04-30,5
2022-04-30,6
];

DateBridge:
Load Distinct MonthEnd
,MonthEnd as Date
, 'Closing' as DateType
resident raw;
Concatenate(DateBridge)
Load Distinct MonthEnd
, Date(MonthEnd+1) as Date
, 'Opening' as DateType resident raw;

MasterCalendar:
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Date', recno()))-1 as mindate,
max(FieldValue('Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('Date');

Post an appropriate sample data in excel , if you need more clarity

Re: Suggestions for reducing rows in order to show Opening and Closing base

vinieme12 — Wed, 18 May 2022 05:52:45 GMT

Just create a bridge table to link the month end date to next month opening date

In charts:

Opening: count({<DateType={'Opening'}>}CustomerID)

Closing: count({<DateType={'Closing'}>}CustomerID)

Difference: count({<DateType={'Opening'}>}CustomerID)-count({<DateType={'Closing'}>}CustomerID)

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Date', recno()))-1 as mindate,
max(FieldValue('Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('Date');

exit script;

Please post some sample data in excel that best represents your actual data ; if you need more clarity

Re: Suggestions for reducing rows in order to show Opening and Closing base

vinieme12 — Wed, 18 May 2022 07:38:45 GMT

You can simply create a bridge table that links one month end date to a monthend and monthstart date

please post some sample data