So this is what I've done, bit rough and ready. We have a need to track invoices across multiple legal entities in different countries on different accounting platforms which is what this is for.
The source data in this example is a view called v_invoice_payment_date. You should be able to swap this out for any data source as long as the data is structured in a way that is 'pivot-able', i.e. organised vertically (you cannot pivot an already cross-tabbed data structure).
First I create an array from the column names of the query:
Then I put that in a table
You then select a vertical value, which needs to be the same as the 'pivot category', the horizontal value, and then the pivot value itself. You do not 'save' this table in any way; a later step uses recordUpdates to access what's been checked.
In parallel I have this transformer which turns the SQL input into an array. It also applies a bunch of filters based on some UI components but these are optional, the only mandatory thing it does is apply formatDataAsArray to the original v_invoice_payment_date data source.
This is the main chunk of code. I use a timestamp as the prefix for the column headings so that they should never duplicate. I then create a 'synthetic' column heading for my first row.
// Add sequence number
// Generate the columns from a number of header columns
// FORCE A FIRST ROW THAT HAS ALL OF THE DATES IN THE RIGHT ORDER WHEN THE APP IS LOADED TO PREPARE THE TABLE, WHICH THEN GETS OVERWRITTEN BY THE REAL QUERY
const x_uniq_runtime = "___________________" + Date.now().toString() + "_"
const x_random = x_uniq_runtime + "total"
const x_data_input = {{v_date_range_filter.value}}
// The column names and whether they have been selected
const x_table_object_keys = {{table_object_keys.recordUpdates}}
.filter(x => x['Pivot Category'] === true)
.map(x => x['Object Key'])
const x_pivot_category_vertical = {{table_object_keys.recordUpdates}}
.filter(x => x['Pivot Category Vertical'] === true)
.map(x => x['Object Key'])
const x_pivot_category_horizontal = {{table_object_keys.recordUpdates}}
.filter(x => x['Pivot Category Horizontal'] === true)
.map(x => x['Object Key'])
const x_table_pivot_value = {{table_object_keys.recordUpdates}}
.filter(x => x['Pivot Value'] === true)
.map(x => x['Object Key'])
// https://stackoverflow.com/questions/54907549/keep-only-selected-keys-in-every-object-from-array
const x_data_categories = x_data_input
.map(x =>
x_table_object_keys.reduce((acc, curr) => {
acc[curr] = x[curr];
return acc;
}, {})
);
const x_data_categories_uniq = _.uniqWith(x_data_categories, _.isEqual) // using lodash, works.
// GENERIC: create array of unique combinations of x, y and the value
const x_pivot_data = x_data_input
.map(
({[x_pivot_category_vertical]:pivot_category_vertical,
[x_pivot_category_horizontal]:pivot_category_horizontal,
[x_pivot_category_horizontal]:pivot_category_horizontal_label,
[x_table_pivot_value]:pivot_value}) =>
({['pivot_category_vertical']:pivot_category_vertical,
['pivot_category_horizontal']:x_uniq_runtime + pivot_category_horizontal,
['pivot_category_horizontal_label']:pivot_category_horizontal_label,
['pivot_value']:pivot_value})
)
// GENERIC: Create horizontal dimension
// n --> item in array, i --> index of item, a --> array reference, (in this case "list") (note x is different to my use)
const z_dim = x_pivot_data
.map(x => x.pivot_category_horizontal)
.filter((n,i,x) => x.indexOf(n) === i)
.sort()
var pivot_value = 0;
var array_out = []
var array_out_formatted = []
var o_row_object = {};
var o_row_object_formatted = {};
// CREATE HEADER ROW, BIT CLUNKY
let first_column_name = Object.keys(x_data_categories_uniq[0])[0] // First key of first object in array
const z_dim_labels = x_pivot_data
.map(
({['pivot_category_horizontal']:pivot_category_horizontal,
['pivot_category_horizontal_label']:pivot_category_horizontal_label}) =>
({['pivot_category_horizontal']:pivot_category_horizontal,
['pivot_category_horizontal_label']:pivot_category_horizontal_label})
)
.sort()
const z_dim_labels_uniq = _.uniqWith(z_dim_labels, _.isEqual) // using lodash, works.
o_row_object = {}; // blank out
o_row_object_formatted = {}; // blank out
z_dim.forEach(function (y_item, y_index) {
let pivot_value = z_dim_labels_uniq
.filter (x => x.pivot_category_horizontal === y_item)
.map(x => x.pivot_category_horizontal_label)
Object.assign(o_row_object, {[first_column_name]: first_column_name, [y_item]: pivot_value});
Object.assign(o_row_object_formatted, {[first_column_name]: first_column_name, [y_item]: pivot_value.toLocaleString(undefined, { maximumFractionDigits:0})});
})
Object.assign(o_row_object, {[first_column_name]: first_column_name, [x_random]: 'row_total'});
Object.assign(o_row_object_formatted, {[first_column_name]: first_column_name, [x_random]: 'row_total'});
array_out.push(o_row_object);
array_out_formatted.push(o_row_object_formatted);
const x_slice_start = x_table_object_keys.length
const x_slice_end = z_dim.length
// MAIN LOOP
x_data_categories_uniq.forEach(function (x_item, x_index) {
o_row_object = {}; // blank out
o_row_object_formatted = {}; // blank out
x_row_total = 0; // blank_out
z_dim.forEach(function (y_item, y_index) {
let pivot_value = x_pivot_data
.filter(x => x.pivot_category_vertical === x_item[x_pivot_category_vertical] && x.pivot_category_horizontal === y_item)
.reduce((prev, cur) => prev + cur.pivot_value, 0);
Object.assign(o_row_object, x_item, {[y_item]: pivot_value});
Object.assign(o_row_object_formatted, x_item, {[y_item]: pivot_value.toLocaleString(undefined, { maximumFractionDigits:0}) });
x_row_total = x_row_total + pivot_value;
})
// row total
Object.assign(o_row_object, x_item, {[x_random]: x_row_total});
Object.assign(o_row_object_formatted, x_item, {[x_random]: x_row_total.toLocaleString(undefined, { maximumFractionDigits:0})});
array_out.push(o_row_object);
array_out_formatted.push(o_row_object_formatted);
})
// END OF MAIN LOOP
// Column totals
o_row_object = {}; // blank out
o_row_object_formatted = {}; // blank out
x_row_total = 0; // blank_out
z_dim.forEach(function (y_item, y_index) {
let pivot_value = x_pivot_data
.filter (x => x.pivot_category_horizontal === y_item)
.reduce((prev, cur) => prev + cur.pivot_value, 0);
Object.assign(o_row_object, {[first_column_name]: "col_total", [y_item]: pivot_value});
Object.assign(o_row_object_formatted, {[first_column_name]: "col_total", [y_item]: pivot_value.toLocaleString(undefined, { maximumFractionDigits:0})});
x_row_total = x_row_total + pivot_value;
})
// Total row total (duplicated code)
Object.assign(o_row_object, {[first_column_name]: "col_total", [x_random]: x_row_total});
Object.assign(o_row_object_formatted, {[first_column_name]: "col_total", [x_random]: x_row_total.toLocaleString(undefined, { maximumFractionDigits:0})});
array_out.push(o_row_object);
array_out_formatted.push(o_row_object_formatted);
return array_out_formatted
The next table uses this as the data source
Which gives this, in correct date order:
The actual column names look like this. I was originally prefixing them with a string of underscores to stop them being visible, but it's basically timestamp then date, which is intrinsically sorted in that order in the query, therefore they will always come out in the right order in here:
I then realized that I can make the header text and header background the same to hide the actual column names:
PLEASE HANDLE WITH CAUTION, it's unfinished / a bit fragile so make sure you don't rely on the output. Also I'm sure just the column header generation bit could be stripped out without needing the full pivot functionality. And there's definitely a more elegant way to do the central loop, that will have to wait for v0.2.
Hope that's useful
Dominic
