Question

How to apply formatting to pivot table when writing to excell using dynamic writer

  • 20 July 2022
  • 1 reply
  • 7 views
N
Badge

I have a dataset with 3 attributes (REF_ID, orde, DMdrempel) and one (DHMdrempel) has formatting (colouring) applied.

imageThere is a 1 to many relation between REF_ID and orde, a 1to1 between DHMdrempel and orde (although sometimes values of DHMdrempel can be identical).

I want to create a pivottabel as this one but with the formatting applied:

imageI apply an attributepivoter with parameters set as follows: group rows by REF_ID, group colums by orde and attribute to analyze is DHMdrempel.

Because of automation purposes and because I don't know the (maximal) number of 'orde'/columns, I cannot use the attributeExposer (or at least I think I can't). I therefore use a Dynamic writer to create my excel file.

But I want to include the formatting (colouring) of the DHMdrempel into my pivotted table but I can't find out how to do this.

Can you help me?

1 reply

dmitribagh
Rank
Userlevel 2
Badge +11

Hi @neel_d​,

 

I think this is possible. Check the attached template. It reads a tab-separated CSV file, which can have an arbitrary number of columns (feel free to add or remove them), and writes to Excel using the dynamic writer mode. The values are colored depending on whether they exceed 50 or not (Pink vs Green).

The trick is to explode the attributes. This gives us a table with pairs of values, attribute_name - attribute_value. We can style conditionally based on the value of attribute_value , this will create the attribute_name.formatting attribute, which is necessary when styling Excel data per cell. Then we restore the original features and create a schema feature, which contains the necessary attributes and the accompanying attribute_name.formatting attributes. Now when we send the data to Excel writer, we get our values colored. I annotated the workspace as much as possible, but if you have more questions, please let me know. Note it looks like we have a bug (or I don't see how to change it) - the written columns are collapsed to zero width and need to be expanded.

 

Dmitri

 

 

Reply


Community Stats

28,723
Posts
109,510
Replies
36,559
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.