Pivottable macro , can we rename the column headings?

[Nags Subramanian]

i have pivot produced through a pivottable macro. can we rename the column names just like we do in excel pivot

[Stiltsoft support]

Hi @Nags Subramanian ,

Firstly gave you the hint in your previous question and then came across this one: wrap your Pivot Table macro in the Table Transformer macro and rename your columns as you wish:

SELECT T1.'Long column name coming from pivot' AS 'New beautiful name'

FROM T1

[Stiltsoft support]

Hi @Janice  ,

Please add some custom tags for us:

• table-filter
• table-filter-and-charts
• table-transformer

Thank you!

[Nags Subramanian]

Thank you.

[Nags Subramanian]

Hi, got stuck with an issue on this please. could you help.

Below is my first table using a table transformer macro. In this i have coloured the Story End column based on component status.

Below is the pivot table produced based on the above table with sort order as column headings. i need to bring in the component status since i need the coluring based on that column. hence each application is displayed on multiple lines based on status.

Now i applied a table transformer macro on top of this pivot to display the column names in more readable way.

2 issues:

1. How to show the dates grouped in one line for one application?

2. How to colour the date column based on the component status.

code currently used is as below:

SELECT T1.'ICTO', T1.'Application Name',
min(T1.'Literal of Story End 1 - Design') as 'Design',
min(T1.'Literal of Story End 3 - Onboard') as 'Onboard',
min(T1.'Literal of Story End 4 - Test') as 'Test',
min(T1.'Literal of Story End 5 - Deploy') as 'Deploy',
min(T1.'Literal of Story End 6 - Cut over') as 'Cut over'
FROM T1 group by T1.'ICTO', T1.'Application Name', T1.'Component Status'

any suggestions please !

[Stiltsoft support]

What concerns the first question, maybe I've got it wrong: if you have, for example, two dates for the same application and their component status is the same, then you'll get these two dates in one cell of your Pivot Table macro.

As I see from your screenshot, in your Pivot Table macro each date is in a separate cell (each date has a unique set of status-application-ICTO).

The same picture you have after the Table Transformer macro: the only difference is that all the merged cells (ICTO, Application Name) will be unmerged again. This is how the macro works - you need to choose if you want a "beautiful" pivot table or a colored table.

As you show all the dates for your Pivot Table (I see the Operation Type as Literal) you don't need "min" or "group" in the SQL query. The idea was to do everything in your Pivot Table macro with a more friendly UI and use the Table Transformer macro just for coloring and renaming your columns.

So, to color your columns based on the statuses you need to use a FORMATWIKI function (like you did it in the very beginning for the first table). The only difference is that you need to use the CASE WHEN conditions for each column separately. I mean that you check the status and color the 'Literal of Story End 1 - Design' column, then check the status and color the 'Literal of Story End 3 - Onboard' and so on.

And as you have in you current query, after the "AS" clause you may type in the shortened name of your columns.

[Nags Subramanian]

Hi, Thank you. i am trying this differently now but getting into a different issue in the final transformed table.

code for the 2 highlighted columns are:

T1.'Literal of Story End 1 - Design' as 'Design date',
FORMATWIKI("{cell:bgColor=LightBlue}" +T1.'Literal of Story End 1 - Design' + "{cell}") as 'Coloured date',

same column printed without fomatting and second printed with formatting.

why is the second one printing as numbers instead of the date value please?

[Stiltsoft support]

Please try to add the FORMATDATE function:

FORMATWIKI("{cell:bgColor=LightBlue}" + FORMATDATE(T1.'Literal of Story End 1 - Design') + "{cell}") as 'Coloured date'

The date should be formatted using the date format that is chosen in the Settings tab of your Table Transformer macro.

Or you may show how you want your date to be displayed in the function itself:

FORMATDATE('Column with dates', "M dd, yy")

where "M dd, yy" is your preferred date format.

The current set of numbers is a default UNIX date format in seconds.

[Nags Subramanian]

Hi, Thanks, FORMATDATE usage worked. i have run into another issue where if you could help please.

in some cases, the value in T1.'Literal of Story End 1 - Design')  is 2 or 3 dates together separated by a , eg: 

Dec 09, 2023, Nov 11, 2023, Nov 15, 2023

this is due to the way the pivot table built unfortunately.

in the table transformer macro, How do i split this into individual dates and then display as formatted date like the below. one below the other in the same cell using this command:FORMATWIKI("{cell:bgColor=LightBlue}" + FORMATDATE(T1.'Literal of Story End 1 - Design') + "{cell}") as 'Coloured date'

Dec 09, 2023

Nov 11, 2023

Nov 15, 2023

[Stiltsoft support]

It is done in the Pivot Table macro: please go to the Options tab, scroll it down and locate the Literal value delimiter field.

By default it is comma. But you may choose other options as well: for your case it will be "Line break".

[Nags Subramanian]

Hi Many thanks for your help so far. its almost there. i have one final issue to be resolved please.

i have a pivot table value returned as below. this pivot table setting Operation type as Literal.

To avoid the nasty headers, i am applying a table transformer macro on top of this pivot table to produce like below.

code is like below. this is for the Deploy column. basically since it was returning unix value of the date (though Literal was mentioned as operation type). i applied the below formula to use formatdate or display the literal as it is. some dates are concatenated by ;, which i am printing as it is.

case when T1.'Literal of Story End 5 - Deploy' is not NULL then

case 

when T1.'Literal of Component Status 5 - Deploy' ~ "Done" then

case when LENGTH(T1.'Literal of Story End 5 - Deploy'+" ") = 13 then

FORMATWIKI("{cell:bgColor=LightBlue} " +FORMATDATE(T1.'Literal of Story End 5 - Deploy')+ "{cell}") 

  else FORMATWIKI("{cell:bgColor=LightBlue} " +T1.'Literal of Story End 5 - Deploy'+ "{cell}") 

     end

end

there are few problems i am encountering:

1. in some cases LENGTH(T1.'Literal of Story End 5 - Deploy') returns nothing, but when i use LENGTH(T1.'Literal of Story End 5 - Deploy' + " ") return a value of 14. See the Test column above. i guess its counting the length of unix value which is 13,  plus the space i am adding, and hence 14. in the cases where the value coming as the date as literal, length is coming as 12, and hence i have added in the code the check for 13 by adding a " ".

2. for some values where the value is concatenated like Mar 22, 2024; Mar 14, 2025, for the deploy column, above code returns as 27 correctly (12+ 12+2(", "for comma and space in between, and the space i am adding at the end).

if i apply the similar formulae for Test for a concatenated date like above, column values instead of Deploy. the length is returning as 14 instead of 27.

Hence i am not able to decisively apply the format date or display as literal as it is at the moment. 

could you please help on pointing out what i am dong is wrong?

thanks.

[Stiltsoft support]

Unfortunately, I couldn't reproduce the issue where the Table Transformer macro returned unix date format after the Pivot Table macro. So, as in your first example you had one date per cell, the FORMATDATE function served as a kind of a workaround.

For your current case when you have different numbers of dates per cell, you may try to use the simple FORMATWIKI function for coloring from the first example (when you color by status and don't pay attention at the string length) but change pluses to commas:

FORMATWIKI("{cell:bgColor=LightBlue}", T1.'Literal of Story End 1 - Design', "{cell}") ...

Commas help to preserve initial cell formatting and your dates coming from the Pivot Table should be treated as simple strings.

Besides if you choose a line break as a Literal value delimiter in the Pivot Table, commas will preserve it and your dates will be positioned on separate rows in a cell.

[Stiltsoft support]

If this doesn't help, I can suggest referring to our support. Attach a page storage of your page (upper right corner -> menu ... -> View storage format) and we'll be able to recreate exactly your page with the source table and macros. 

Also as you have Server/Data Center judging by your tags, please check what version of the app you are using. The current one is 10.3.0 - if yours is an older one, please update.

[Nags Subramanian]

thank you. i have implemented something similar to this just now. i appended the dated value in the source table with some literal and hence it is treated as literal always. at the final table transformer table i replaced the literal i added with spaces and printing it. it kinda works.

is there a way for this post be hidden from external people. i have shared some screenshots with some company data of application, and would like this to be hidden.

or can we atleast delete the images on all posts?

thank you and apologies.

[Stiltsoft support]

I think you can edit your initial question and all your comments and remove the images. When you click on the thee dots menu near each entry, you will see an "Edit reply" option.