NPRINTING

[Amar2]

Hello

I am using pagination in excel NP template. I want to generate table of contents with all sheet names as in pagination. When I apply pagination different sheet generates in excel. Now how to generate table of contents of those sheets names of pagination. So if pagination contains 5 sheets, Table of contents will generate 5 sheet names as index.. if pagination contains 10 sheets then TOC generate 10 names as index. So index will take dynamic pagination value.

Thanks

[Amar2]

Hello Lech,

I am sorry , I cannot attach sample file due to policies.

I want to create TOC for excel N printing. Your suggestion for creating levels and adding hyperlink function is great but in my data there are many combinations and excel sheet names are generating by itselt upto 31 chars.

Pagination value contains combination of  3 different fields  let's say A(different number of characters for different values),B and C.

For ex-  set of 10 values in A could be same, then another set of 10 values are same and so on.

So the sheet names are generating value in A ,A1,A2,A3.

I don't have control over sheet names. 

This is not constant, Sometimes the value in A and B is different so there is different pattern by Excel to generate sheet names.

I cannot change the values of Page field as these values are used to generate reports.

I used index function in excel template to generate TOC but it is only giving one sheet name as <page>

[Lech_Miszkiewicz · Accepted solution]

Hi @Amar2 

Well i did not expect you to attach a sample file with your data - I thought you could just mock something up just like i did below - anyway - another lesson for the future. 

So now - I disagree with this sentence (marked in red):

I want to create TOC for excel N printing. Your suggestion for creating levels and adding hyperlink function is great but in my data there are many combinations and excel sheet names are generating by itselt upto 31 chars.

I am fully aware that your sheets are driven by data in field used for paging and that is why I told you that we have to work with this field and create on QlikView side equivalent of field which will be limited to 31 characters (that was my suggestion here)

See example below (I am providing you with the script as well so you can copy paste and test):

PageTable:
        Load
        	Page_Field_temp
            ,Left(Page_Field_temp,27)&' '&Num(Autonumber(Page_Field_temp,'Page_Field_temp'),000)	as Page_Field_with_Num
        Inline
        	[
            	Page_Field_temp
            	I found that my sheet names which are pagination names are very long
                I found that my sheet names which are pagination names are very
                and they are truncating. That could be the reason why the pagination hyperlinks long names are not referencing
                Can sheet names be unlimited long? If not then in hyperlinks can we give sheet1, sheet 2, sheet3 like this
            ]
        ;

1. Field Page_Field_temp - has long values which when used with NPrinting page would be truncated - this is what you are currently using and yes, it creates problems because NPrinting will truncate those and likely if the truncated values are the same do some more things to it. And yes - this will create problems, so I suggest to create another field:

2. I then created Page_Field_with_Num field which I truncated by myself in Qlik load script and add autonumber function to  make sure unique values are retained (note values: "I found that my sheet names 001" and "I found that my sheet names 002" as they represent different Page values being: "I found that my sheet names which are pagination names are very" and "I found that my sheet names which are pagination names are very long"). You can see this relationship in below table (click on image to enlarge it so it is not blurry)

So now in NPrinting I use my truncated field (which I have full control over and I truncated it to always have max 31 characters so it can be used in excel sheet name without modification. 

Final Template:

Final Output:

 - Full name used in Index and referencing short-truncated name of the sheet

- Short name + number used in Page

Also output file attached below.

I am not sure what else I can suggest to you. Those are excel limitations and you can only workaround it. It is worth noting that my approach does not need any sophisticated approach. The other approach could be to write a macro , but I dont like it since it may not work on all devices and adds up to things which potentially will not work.

you also say:

I cannot change the values of Page field as these values are used to generate reports.

I am not asking you to change Page field value - I understand you may require to show full name in some other places of that report or even on each individual page. In fact I am still using long field name in the Index sheet to show full names. Therefore I am suggesting to create another field which you can use in excel Page tag. In your current situation your field is getting truncated anyway when values are being passed to sheet name so there is no harm truncating it directly in Qlik,

If this is still a trouble to you I then suggest to contact your Qlik partner who can provide you with a consulting service and build that for you. Since solution is quite simple it would be no more then few hours of consulting. 

cheers