Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
jwaligora
Creator II
Creator II

Value formatting in table with non-static column names (also NPrinting)

Hi.

To be fair, I have no idea if this question belongs in scripting, publishing, or here...

I have some data that is required in the format:

Metric Name |  2020-Jul | 2020 - Jun | 2020 - May | etc...
M1 | 332 | 432 | 634 | ...
M2 | 24.5% | 15.9% | 36.4% | ...

Where the month column names will update (daily) to include the last/latest 2 years of metrics.

The values (under the month labels), corresponding to each Metric Name vary based on the metric - some should be formatted as integers, some as real numbers, some should include % symbols. 

Thus far I have all the calculations along with the table structure defined at script level. It delivers exactly what I'm looking for, BUT, when I use NPrinting and generate an Excel file from this table all the values are flagged as "number represented as text". Is there a way to avoid this?

...there is also a sub-issue here:
in the script I use
                     round([myVal]*100, 0.01) & '%'
in order to properly display the % values. Without these operations the default front-end representation seems to be 'integer'. Even when I apply round() without implicitly casting it to string, I still just get an integer (in my case all the values are in the 95%-99% range, so the integer returned is always 1). This would be easy to address if I could just modify the column format on the front-end BUT
          (A) I only need this format in some of the rows and not others
          (B) As time goes by, the existing columns will be replaced by new month values (2020-Aug, 2020-Sep, etc.) so any format definitions tied to column names won't work in the long run. 

I'm open to suggestions...preferably ones that don't require a complete script re-write 😬

Thanks,
J.

 

Labels (6)
4 Replies
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @jwaligora 

Well, This is of course NPrinting related question, but the information you are providing is not enough. We need to know following:

  • Are you using QlikView or Qlik Sense (provide also the exact version you use)
  • Provide the exact version of NPrinting
  • Provide methodology of how you build your chart in Qlik? What is your measure and dimension?
    1. Are you using pivot table? or..
    2. Are you using straight table with Set Analysis for each month (24 measures)
  • Are you using num() function in your chart?

Now, I want to point out that doing rounding in script is probably not the best idea. Normally for the requirement like yours in Qlik I would use Measure loaded as Dual value as a dimension in straight table and I would have a values coming through as a measures like...

num(
Pick(Measure,Sum(Revenue), Sum(Cost)/Sum(Revenue)),
Pick(Measure,'$#,##0;-$#,##0','#,##0%')
)

This approach allows you to have any format on any line in QlikView/Qlik Sense table. I would have 24 measures like this (24 columns), which then I can easily use as reference in NPrinting. Labels for those columns can be calculated directly in NPrinting as formulas.

Now, because you are using Excel template I would go further with the customisation and I would disable "Keep Source format"  and instead use Excel conditional formatting based on row (not column!!!). For that I typically add in my Qlik Table which is used as a source for NPrinting extra column which is like: 

Pick(Measure,sales, margin,...etc..)

If my measure sales needs to be always formatted as $ I can use Excel conditional formatting to achieve this based on that column, the same for any other format, font style and colour etc... It becomes then typical excel exercise!

hope this helps

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
jwaligora
Creator II
Creator II
Author

Hi,

Thank you for replying and apologies for delay; 2 wk "vacation" refinishing my floors. 

QlikView Nov 2017 SR9 v.12.20.21000.0
Qlik NPrinting April 2018 Version: 18.9.5

I use a Table Box since everything else happens in the script (I started trying to make it work in a chart, but gave up due to too many per-column tweaks...24 non-static month columns worth of maintenance). 

There's a lot of prep work pulling in data from various sources, but the pertinent part of the script (less the aforementioned rounding which happens prior) is this:

/*
The steps in this tab use the Metrics table (which already holds all the pertinent data),
and transform it into the desired format, where each month is listed in a separate column.

Approach is heavily based on https://community.qlik.com/t5/New-to-QlikView/Row-values-as-fields-opposite-of-crosstable/m-p/273173#78337
(2010-08-23 / 10:21 AM reply by user "/Not applicable"...most likely formerly identified as Alex)
*/

/*
Using data from Metrics table, generate a bunch of mini-tables, one table per Mo, with:
   Table Name = temp1.<Mo>
   Column 1 Name = Metric // (holds metric names)
   Column 2 Name = <Mo>   // (holds Va corresponding to Mo, for each metric name in Metric column)
*/
temp1:
generic load // Column order matters in order for this transform to work.
	Me as Metric,
	Mo as Month,
	Va as Value
resident Metrics
;

/*
Create the first column (with the metric names as values) in the final Result table.
*/
Result:
LOAD Distinct Me as Metric Resident Metrics;


FOR Each i in FieldValueList('Mo') // This field comes from the Metrics table
TableList_prep:
LOAD 'temp1.$(i)' as TabName, $(i) as SortVal,  date($(i),'YYYY-MMM') as CharVal AUTOGENERATE 1;
NEXT i

drop table Metrics;

/*
Sort the value in TableList as a prerequisite for eventual sort of Yr-Mth columns based on Load order.
*/
TableList:
LOAD Distinct TabName as Tablename, SortVal as Sorter, CharVal as MoName Resident TableList_prep Order by SortVal;

DROP Table TableList_prep;

/*
Loops though the mini-tables generated by the generic load 
and appends them to the final Result table using a JOIN operation.
*/
FOR i = 1 to FieldValueCount('Tablename')
	LET vTable = FieldValue('Tablename', $(i)); 
	
	/* 
	This block renames the Yr-Mth column in the currently processed 
	mini-table (created by the generic load) from its numerical value
	to a text value. 
	Note: Why use a number in the first place? The numerical represenation was used to enable sort control and bypass parsing issues associated with presence of a dash inside values stored in variables.
	*/
	LET vNumMonthLabel = FieldValue('Sorter', $(i));
	LET vCharMonthLabel = date(FieldValue('Sorter', $(i)),'YYYY-MMM');
	RENAME Field '$(vNumMonthLabel)' to '$(vCharMonthLabel)'; 
	
	/*
	This block is required to control the Load order of the Yr-Mth columns,
	which in turn enables correct sorting of said columns.
	...The FOR loop is based on row order of the TableList table.
	*/ 
	STORE $(vTable) into 'TempTransform.qvd' (qvd);
	DROP TABLE $(vTable);
	Temp:
	LOAD * from 'TempTransform.qvd' (qvd);
	
	// Add data for the month being processed to the final table
	LEFT JOIN (Result) LOAD * RESIDENT Temp;
	DROP TABLE Temp;
NEXT i


drop table TableList;

 

I will review/test out your suggestions, hopefully tomorrow; just figured I would push out the context as a sign of life.

Thanks,
J.

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi ,

I think you very much over complicated it. Your approach is interesting but makes whole thing almost impossible to work from NPrinting point of view. You are loosing flexibility of being able to apply formats based on expressions to rows of your data.

regarding your comment: " (...I started trying to make it work in a chart, but gave up due to too many per-column tweaks...24 non-static month columns worth of maintenance...)". i think It is all down to good practise. It just takes 1 parameterised variable as expression to build what you need and then a lot more freedom of how to visualise this data and how to apply formats. 

Looking at the way you have loaded data with generic load I think  you have taken this super static approach which is not ideal for NPrinting. I guess you must be more of SQL guy than QlikView developer - and this is not a bad thing, just an observation as once I was there and I would have done the same thing 🙂

now, because you are using table box I don't think I can help you here. I think it is not worth an effort since I cannot see it working. If you are willing to explore option to use straight table chart I may be able to help and guide you trough. Let me know your thoughts.

 

cheers

Lech

 

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
jwaligora
Creator II
Creator II
Author

Hi,

I'm happy to switch back to a more front-end focused approach, provided the end state is an Excel table described in the original post. The other constraint is sufficient automation such that when a new metric is added, the corresponding update is made in one place rather than across multiple chart columns. Finally, the columns must auto-update/shift based on current date. 

With the source data coming from various places (requiring alignment with other dashboards) getting into the pre-processing would overly complicate the matter, so we can assume the desired metrics come pre-calculated in a single table, just not the desired layout. I attached an Excel file that corresponds to the actual data. To imitate time progression, there are multiple tabs representing source data for a simplified monthly load/refresh (each subsequent one overwriting the previous), with shifting 13 months of data to be displayed in the final table at any given point in time.

I have some experience with QV, but virtually none with NPrinting, and I've never played with automated row-formatting in Excel.

What should my next steps be?

Thanks,
J.