Spotfire - Color by dynamic group - use $map() and rank()

Sometimes you want to see how data changes by grouping data into different groups. Groups changes by using sliders.

This is what you can use in "color by":

You need first to create new rank columns based on sorted data.

If you want to select multiple columns, first, create new columns (avg/sum rank), and then re-rank the new column.

Useful link: (add calculated/binned/tagged columns)

https://www.youtube.com/watch?v=oorLo7Hrt6g

SQL - random characters expression

Use  %[^0-9ED.+-]%

e.g.

EXCEL - Find position of the Nth space in a string

Find position of first space	=FIND(" ",A1)
Find position of second space	=FIND(" ",A1,FIND(" ",A1)+1)
Find position of third space	=FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)
Find position of forth space	=FIND(" ",A1,FIND(" ",A1,FIND(" ",A1, FIND(" ",A1)+1)+ 1)+1)

SQL - Passing multiple values through one parameter in a stored procedure

...............................

SSIS - Execute Package Task

We can execute a package through a "Execute package task".

Just don't forget to pass the parameter needed for another package:

SQL - Find the longest common sub-string match

Use charindex()

Also, when there are different join cases, can use case in the function, like right(string, case when...)

e.g.

Spotfire - useful frame for dashboard control (HTML and JS)

HTML

<table style="width: 200px; border-collapse: collapse;"> <tbody> <tr> <td> <table class="panel_table on_hover" id="navigation" style="width: 100%; border-collapse: collapse;"> <tbody> <tr> <td class="cell panel_title_cell panel_header on_click" id="navigation01" style="padding: 8px; border-image: none; color: white; font-size: 1.1em; cursor: pointer; background-color: rgb(0, 75, 154);"> <strong class="menu_icon" id="navigation_menu_icon">+</strong> <strong>Navigation</strong> </td> </tr> <tr> <td> <table class="panel_table" style="width: 100%; border-collapse: collapse;"> <tbody> <tr> <td class="cell panel_control" id="navigation_menu_panel" style="padding: 8px; border-image: none; display: none; background-color: white;"> Click on link to go to page: <div style="padding-top: 8px;"> <div style="padding-top: 8px; padding-left: 16px;"> <SpotfireControl id="84296633e2b247b886d9e630a9ff201a" /> <div style="height: 8px;"></div> <SpotfireControl id="0ac76cec955d480cbb7ddc4e4282881d" /> <div style="height: 8px;"></div> <SpotfireControl id="28480fc6e6f642acb03a6d89a935960d" /> <div style="height: 8px;"></div> <SpotfireControl id="dac9656410e544b2b4b4fdf90e4b13dd" /> <div style="height: 8px;"></div> <SpotfireControl id="a3e016eb88b94fe58a25dca2b6e9a546" /> <div style="height: 8px;"></div> <SpotfireControl id="31c409772438407f8548756ccba848e1" /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </div> <div style="margin-top: 8px;"> <SpotfireControl id="8ba3df5bec144dc8bf00f6e1dc26903c" /> </div> </div> </td> </tr> </tbody> </table> </td> </tr> </tbody> </table> </td> </tr> <tr> <td class="row_divider" style="height: 4px;"> </td> </tr> <tr> <td> <table class="panel_table" style="width: 100%; border-collapse: collapse;"> <tbody> <tr> <td class="cell panel_title_cell panel_header on_click" id="control" style="padding: 8px; border-image: none; color: white; font-size: 1.1em; cursor: pointer; background-color: rgb(0, 75, 154);"> <strong class="menu_icon" id="control_menu_icon">-</strong> <strong>Control</strong> </td> </tr> <tr> <td id="control_menu_panel"> <table class="panel_table" style="width: 100%; border-collapse: collapse;"> <tbody> <tr> <td> <table class="panel_table" style="width: 100%; border-collapse: collapse;"> <tbody> <tr> <td class="cell panel_header_cell panel_header on_click" id="distribution" style="padding: 8px; border-image: none; color: rgb(6, 53, 122); font-weight: bold; cursor: pointer; background-color: rgb(165, 189, 208);"> <strong class="menu_icon" id="distribution_menu_icon">-</strong> <strong>Company activity</strong> </td> </tr> <tr> <td class="cell panel_control" id="distribution_menu_panel" style="padding: 8px; border-image: none; display: block; background-color: white;"> <div style="padding-bottom: 8px;"> <strong>Select top permits operators by:</strong> <br><br> <SpotfireControl id="e2743543d28b448b8fd258d9b11f82cc" /> <br><br> <DIV id = "norm_by_permit" style="display:none"> <div style="height: 10px;"></div> <SpotfireControl id="c5df2e0865814f45b0c0a7d09539811a" /> &nbsp <br><br> From: &nbsp <span id = 'dt1'> <SpotfireControl id="64f3d155858f4e908fee8ec1c4f4361d" /> </span> <span id='dt1picker'></span> <br> <br> To: &nbsp&nbsp&nbsp&nbsp&nbsp <span id = 'dt2'> <SpotfireControl id="d2b3ffc3c3e340919f315c8f3a6d62ac" /> </span> <span id='dt2picker'></span> <br><br> </DIV> <DIV id = "norm_by_completion" style="display:none"> <div style="height: 10px;"></div> <SpotfireControl id="172551d79d26410f913adb83f1d39034" /> &nbsp <br><br> From: &nbsp <span id = 'dt3'> <SpotfireControl id="41680b0079994e5f93b352b816724a4e" /> </span> <span id='dt3picker'></span> <br> <strong></strong> <br> To: &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <span id = 'dt4'> <SpotfireControl id="2c3887295e9b466ebfeb4c4cda4cecfc" /> </span> <span id='dt4picker'></span> <br><br> </DIV> <DIV id = "norm_by_production" style="display:none"> <div style="height: 10px;"></div> <SpotfireControl id="090e7bb449c94bfebcd1c625f4950902" /> &nbsp <br><br> From: &nbsp <span id = 'dt5'> <SpotfireControl id="783bc7ec9fb14acd863fc0d0f93fd7a4" /> </span> <span id='dt5picker'></span> <br> <strong></strong> <br> To: &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <span id = 'dt6'> <SpotfireControl id="e630e756213a4be3ab176d9ed02bed98" /> </span> <span id='dt6picker'></span> <br><br> </DIV> <strong>View by:</strong> <br><br> <SpotfireControl id="22927b55ca5642e98b7a146b0fcf3c1a" /> <br><br> <strong>Filter by:</strong> <br><br> <SpotfireControl id="33f0653484294e1a8665d9570a5e09ee" /> </div> </td> </tr> </tbody> </table> </td> </tr> <tr> <td> <table class="panel_table" style="width: 100%; border-collapse: collapse;"> <tbody> <tr> <td class="cell panel_header_cell panel_header on_click" id="well_selection" style="padding: 8px; border-image: none; color: rgb(6, 53, 122); font-weight: bold; cursor: pointer; background-color: rgb(165, 189, 208);"> <strong class="menu_icon" id="well_selection_menu_icon">-</strong> <strong>Well Selection</strong> </td> </tr> <tr> <td class="cell panel_control" id="well_selection_menu_panel" style="padding: 8px; border-image: none; display: block; background-color: white;"> <div style="text-align: center; padding-bottom: 8px;"> <SpotfireControl id="7d4f003c3d254120ad6368b2e6548a27" /> <SpotfireControl id="aa78c181b6d84d059440f2da5c546ec0" /> <SpotfireControl id="7f03ddd04e9048ddb717e7e7ab42701d" /> </div> </td> </tr> </tbody> </table> </td> </tr> <tr> <td> <table class="panel_table" style="width: 100%; border-collapse: collapse;"> <tbody> <tr> <td class="cell panel_header_cell panel_header on_click" id="api_filter" style="padding: 8px; border-image: none; color: rgb(6, 53, 122); font-weight: bold; cursor: pointer; background-color: rgb(165, 189, 208);"> <strong class="menu_icon" id="api_filter_menu_icon">+</strong> <strong>API / Well ID Filter</strong> </td> </tr> <tr> <td class="cell panel_control" id="api_filter_menu_panel" style="padding: 8px; border-image: none; display: none; background-color: white;"> <div style="text-align: center; padding-bottom: 8px;"> <span style="font-size: 0.8em;">Enter one API per row (14 digits):</span> <div style="color: black; font-size: 0.8em;">Eg 42177334520000</div> <SpotfireControl id="c19e7a212b9e488db19c707f5fd23daa" /> <br> <div style="color: black; font-size: 0.8em;">(up to 10,000 wells)</div> <div style="height: 10px;"></div> <SpotfireControl id="77a0396f8c92464ba9b8cacd1974d51c" /> <SpotfireControl id="651187f8a0b04ea0bf3760fbf92b6ff7" /> </div> </td> </tr> </tbody> </table> </td> </tr> </tbody> </table> </td> </tr> </tbody> </table> </td> </tr> <tr> <td class="row_divider" style="height: 4px;"> </td> </tr> </tbody> </table> <table width="200" border="0"> <tbody> <tr> <td align="center"> <SpotfireControl id="d224b9f291234890a84ffe750715b6ba" /> &nbsp;wells included. <br> </td> </tr> <tr> <td align="center"> <br> </td> </tr> <tr> <td align="center"> <SpotfireControl id="e65da73d6193408289c8fc4bd625d282" /> </td> </tr> </tbody> </table>

JS

//format divider //style='height:8px' var items = document.querySelectorAll('.row_divider'); for (i = 0; i < items.length; i++) { items[i].style.height = '4px'; } //format small tables //border-collapse:collapse; width='100%'; items = document.querySelectorAll('.panel_table'); for (i = 0; i < items.length; i++) { items[i].style.borderCollapse = 'collapse'; items[i].style.width = '100%'; } //format cell //style='adding:8px;border:1px solid #adafb2;' items = document.querySelectorAll('.cell'); for (i = 0; i < items.length; i++) { items[i].style.border = '1px solid #adafb2'; items[i].style.padding = '8px'; } //format box note //style='background-color:#e1e1e3;' items = document.querySelectorAll('.box_note'); for (i = 0; i < items.length; i++) { items[i].style.backgroundColor = '#e1e1e3'; items[i].style.textAlign = 'center'; } //.panel_title_cell //style='font-weight:bold;font-size:1.5em;' items = document.querySelectorAll('.panel_title_cell'); for (i = 0; i < items.length; i++) { items[i].style.backgroundColor = '#004b9a'; items[i].style.color = 'white'; items[i].style.fontSize = '1.10em'; } //format header cell //style='background-color:#00a4e3;color:white; items = document.querySelectorAll('.panel_header_cell'); for (i = 0; i < items.length; i++) { items[i].style.backgroundColor = '#a5bdd0'; items[i].style.color = '#06357a'; items[i].style.fontWeight = 'bold'; } //format icon //style='vertical-align:middle;font-size:105%;' items = document.querySelectorAll('.panel_icon'); for (i = 0; i < items.length; i++) { items[i].style.verticalAlign = 'middle'; items[i].style.fontSize = '1.05em'; } //format control panel //style='background-color:white;' items = document.querySelectorAll('.panel_control'); for (i = 0; i < items.length; i++) { items[i].style.backgroundColor = 'white'; } //format div highlight on hover items = document.querySelectorAll('.on_hover_highlight'); for (i = 0; i < items.length; i++) { items[i].style.paddingTop = '4px'; items[i].style.paddingBottom = '4px'; items[i].onmouseenter = function() { this.style.backgroundColor = 'green'; }; items[i].onmouseleave = function() { this.style.backgroundColor = 'transparent'; }; } //format header div //cursor:pointer items = document.querySelectorAll('.panel_header'); for (i = 0; i < items.length; i++) { //format items[i].style.cursor = 'pointer'; } //var myTimerID = 0; //assign toggle function to header cell (onclick) items = document.querySelectorAll('.on_click'); for (i = 0; i < items.length; i++) { items[i].onclick = function () { // clearTimeout(myTimerID); togglePanelDisplay(this,'either'); }; } //assign toggle function to header table (mousemove) items = document.querySelectorAll('.on_hover'); for (i = 0; i < items.length; i++) { items[i].onmouseenter = function () { // clearTimeout(myTimerID); togglePanelDisplay(this,'show'); }; items[i].onmouseleave = function () { togglePanelDisplay(this,'hide'); // myHeader = this; // myTimerID = setTimeout(function(){ // togglePanelDisplay(myHeader,'hide'); // },400); }; } function togglePanelDisplay(objHeader,strMode){ //get id var strID = objHeader.getAttribute('id'); if (strID.indexOf('0') != -1) { strID = strID.substr(0,strID.indexOf('0')); //get string before 0 } //use id to get other elements objIcon = document.getElementById(strID + '_menu_icon'); objPanel = document.getElementById(strID + '_menu_panel'); //use strMode to decide how to display if (strMode == 'show') { objPanel.style.display = 'block'; objIcon.innerHTML = '&#8209;'; } else if (strMode == 'hide') { objPanel.style.display = 'none'; objIcon.innerHTML = '+'; } else { //get current state of display var currentDisplay = objPanel.style.display; //switching display state and icon if (currentDisplay == 'none') { objPanel.style.display = 'block'; objIcon.innerHTML = '&#8209;'; } else { objPanel.style.display = 'none'; objIcon.innerHTML = '+'; } } }