Solved: NPRINTING - Qlik Community

Amar2 · ‎2022-02-09

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

Lech_Miszkiewicz · ‎2022-02-15

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

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.

View solution in original post

Lech_Miszkiewicz · ‎2022-02-09

Hi @Amar2

Before I go to answer your question please ask yourself how you would like to search Qlik Community for answers on various topics. Typically you would use different keywords and likely you would like to know which software your question relates to. That being said title of this topic "Nprinting" is meaningless as everything in NPrinting forum is about NPrinting and does not help when searching for topics on community which were already covered. I would create a title like "How to create Table of Contents in NPrinting Excel template when using Page to create sheets with variable names".

Second thing would be to provide us with the exact versions of software you are using being QlikView ver?? and NPrinting being ver?? For NPrinting versions we have dedicated labels on community so they should be used accordingly.

I hope this clarifies basics of community housekeeping and will not be an issue in the future so now we can go to the actual question:

To generate TOC of all values used in Page tag (which creates individual sheets you would have to:

create extra sheet (lets call it index)
bring in field value which you use for paging as either:
- a table (that would require you to create a single column table object in QlikView)
- or as Level
Then you can utilize excel hyperlink function to create links to all sheets as presented below

Sample template:

See gif below for end result:

cheers

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.

Amar2 · ‎2022-02-09

Hello Sir

I will take care while posting next time. I appreciate your solution. I am able to generate TOC now but when clicking hyperlinks, it is giving error ‘reference invalid”. Plz help.

Thanks

Amar2 · ‎2022-02-09

Hello again

I found that my sheet names which are pagination names are very long and they are truncating. That could be the reason why the pagination hyperlinks long names are not referencing to sheet truncated names. Can sheet names be unlimited long? If not then in hyperlinks can we give sheet1, sheet 2, sheet3 like this.

thanks

Lech_Miszkiewicz · ‎2022-02-09

Hi

Length of sheets in Excel is limited to 31 characters - this is Excel limitation (not NPrinting).

Solution to that would be to create another field in your QlikView data model with function like (Left(your_paging_field,31) as NPrinting_Page and use that field in NPrinting Page. Then for the TOC you could create a QV table built of long and short names and use that in the level. Long name would be descriptive and short name would be used for hyperlinks.

As alternative you could use adjusted hyperlink formula to pass only left 31 characters like:

=HYPERLINK("#'"&LEFT(C3,31)&"'!A1",C3)

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.

Amar2 · ‎2022-02-10

Hello

Good morning Sir,

Can you plz let me know what is A1 and #contributing in formula of hyperlink. I am trying to use the hyperlink formula but no success yet. List is generating but hyperlink names are not truncating. Using above formula still long names came and when I click hyperlink it says reference is not valid.

Appreciate

Thanks

Lech_Miszkiewicz · ‎2022-02-10

A1 is a reference of landing/destination cell on destination sheet.

This is now purely excel question (nothing to do with NPrinting) as you just need to iron out simple hyperlink function and pass to it correct values. To do that i mentioned you can have 2 options

- 1 create already truncated names in your Qlik Script

- 2 truncate them using Left() function just like on my example

if something is not working maybe provide attach your sample output file here and we can work it out

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.

Blalock69 · ‎2022-02-11

@LiteBlue wrote:

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

That being said title of this topic "Nprinting" is meaningless as everything in NPrinting forum is about NPrinting and does not help when searching for topics on community which were already covered.

Amar2 · ‎2022-02-13

Hi Lech

Can we change the name of pagination sheets which is dynamically created by excel. No. of sheets generated is also depends on Selection made upfront in QV App. I tried INDEX() function also. No success yet.

Thanks

Lech_Miszkiewicz · ‎2022-02-14

Sorry - I dont understand your requirement.

If you are using <Page> paging then Sheet names are based on values of the field you used in Page node. So you can control values by feeding correct values directly in Qlik.

I also dont understand what do you mean by using Index() function - I have no idea how/where you would use it and what for considering this topic.

As I said before - if you are struggling with something - provide a sample file here so we can work on the same data and the same example.

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.

NPRINTING

QlikView

Related Topics