How to pivot this data table?
I have this data table in my database. Both seller and contract terms are varchar fields and the rest are float fields. I want to pivot this to show the end table like below. However, it s...
Forum Discussion
Hi lalasisi ,
With some work, you could change style etc. with the BloX definition below to achieve transposed pivot table. YMMV - so i'd love to hear if this works for you. Please see the image and code below.
BloX Editor Code:
{
"style": "#table-container66971f8dd755690032cc2e40 { font-family: Arial, Helvetica, sans-serif; border-collapse: collapse; width: 100%; } #table-container66971f8dd755690032cc2e40 td, #table-container66971f8dd755690032cc2e40 th { border: 1px solid #ddd; padding: 8px; } #table-container66971f8dd755690032cc2e40 tr:nth-child(even){background-color: #f2f2f2; } #table-container66971f8dd755690032cc2e40 tr:hover {background-color: #ddd; } #table-container66971f8dd755690032cc2e40 th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #04AA6D; color: white; }",
"script": "",
"title": "",
"showCarousel": false,
"body": [
{
"type": "Container",
"style": {
"padding": "5px"
},
"items": [
{
"type": "TextBlock",
"id": "table-container66971f8dd755690032cc2e40",
"class": "table-container",
"text": "",
"style": {
"overflow": "auto"
}
}
]
}
],
"actions": []
}BloX Configuration Code:
{
"fontFamily": "Open Sans",
"fontSizes": {
"default": 14,
"small": 16,
"medium": 20,
"large": 50,
"extraLarge": 32
},
"fontWeights": {
"default": 500,
"light": 100,
"bold": 1000
},
"containerStyles": {
"default": {
"backgroundColor": "transparent",
"foregroundColors": {
"default": {
"normal": "#000000"
},
"white": {
"normal": "#ffffff"
},
"grey": {
"normal": "#5C6372"
},
"orange": {
"normal": "#f2B900"
},
"yellow": {
"normal": "#ffcb05"
},
"black": {
"normal": "#000000"
},
"lightGreen": {
"normal": "#3ADCCA"
},
"green": {
"normal": "#54a254"
},
"red": {
"normal": "#dd1111"
},
"accent": {
"normal": "#2E89FC"
},
"good": {
"normal": "#54a254"
},
"warning": {
"normal": "#e69500"
},
"attention": {
"normal": "#cc3300"
}
}
}
},
"imageSizes": {
"default": 40,
"small": 40,
"medium": 80,
"large": 160
},
"imageSet": {
"imageSize": "medium",
"maxImageHeight": 100
},
"actions": {
"color": "",
"backgroundColor": "white",
"maxActions": 5,
"spacing": "extraLarge",
"buttonSpacing": 20,
"actionsOrientation": "horizontal",
"actionAlignment": "center",
"showCard": {
"actionMode": "inline",
"inlineTopMargin": 16,
"style": "default"
}
},
"spacing": {
"default": 5,
"small": 20,
"medium": 60,
"large": 20,
"extraLarge": 40,
"padding": 0
},
"separator": {
"lineThickness": 1,
"lineColor": "#eeeeee"
},
"factSet": {
"title": {
"size": "default",
"color": "default",
"weight": "bold",
"warp": true
},
"value": {
"size": "default",
"color": "default",
"weight": "default",
"warp": true
},
"spacing": 20
},
"supportsInteractivity": true,
"imageBaseUrl": ""
}BloX Widget Script Code:
widget.on('buildquery', (w, args) => {
w.style.currentCard = {
"style": `#table-container${w.oid} { font-family: Arial, Helvetica, sans-serif; border-collapse: collapse; width: 100%; } #table-container${w.oid} td, #table-container${w.oid} th { border: 1px solid #ddd; padding: 8px; } #table-container${w.oid} tr:nth-child(even){background-color: #f2f2f2; } #table-container${w.oid} tr:hover {background-color: #ddd; } #table-container${w.oid} th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #04AA6D; color: white; }`,
"script": "",
"title": "",
"showCarousel": false,
"body": [
{
"type": "Container",
"style": {
"padding": "5px"
},
"items": [
{
"type": "TextBlock",
"id": `table-container${w.oid}`,
"class": "table-container",
"text": "",
"style": {
"overflow": "auto"
}
}
]
}
],
"actions": []
}
})
widget.on('ready', (w, args) => {
let rHeaders = []
const table = document.createElement('table')
for (i = 0; i < w.metadata.panel('Items').items.length; i++) {
let tempHeaders = w.queryResult.map((res) => { return res[i].Text })
// tempHeaders.reverse()
tempHeaders.unshift(w.metadata.panel('Items').items[i].jaql.title)
// Create a header row
let headerRow = table.insertRow()
// Add header cells to the header row
for (const header of tempHeaders) {
let headerCell = headerRow.insertCell()
headerCell.textContent = header
}
}
let rValues = []
let l = w.metadata.panel('Values').items.length + w.metadata.panel('Items').items.length
for (let i = w.metadata.panel('Items').items.length; i < l; i++) {
rValues.push(w.queryResult.map((res) => { return res[i].Text }))
}
rValues.forEach((a, i) => {
// a.reverse()
a.unshift(w.metadata.panel('Values').items[i].jaql.title)
})
// Create a row for each array of values
for (const values of rValues) {
const row = table.insertRow()
// Add a cell for each value in the array
for (const value of values) {
const cell = row.insertCell()
cell.textContent = value
}
}
// Add the table to the document
$(`#table-container${w.oid}`).empty()
$(`#table-container${w.oid}`).append(table)
})
Alternatively - RAPID BI also has Adaptive Pivot Tables plugin, that could be used to achieve this (may be overkill for simple transpose though, but it allows viewer modification of the configuration of the table). Happy to discuss further - please reach out if you'd like to.
Let me know how you go?
lee_hub above may be useful for you too - let me know?
