topic Excel Formatting Issues in Qlik NPrinting

Excel Formatting Issues

jayabedin — Thu, 09 Nov 2017 02:08:48 GMT

Hi,

I am using NPrinting 17.3 I have a Pivot table in Qlikview, This is formatted accordingly in Qlikview. In NPrinting, I am keeping the source format.

But once I put this into NPrinting Excel, format changes. I am definitely missing something. Any help on this will be highly appreciated. Please find attachment that shows QV and NPrinting output.

Regards

Jay

Re: Excel Formatting Issues

Lech_Miszkiewicz — Thu, 09 Nov 2017 03:45:43 GMT

Well,

From what i see formats are kept as per your request - they are the same (the same decimal points, the same number or % formats etc).

The problem you have is that excel automatically recognizes text values and aligns them to the left. Text values will be the ones which have in the same column % and numbers. The reason behind this is a formula you have used in QlikView to build an expression ( i believe you used num function for the column where mismatches are shown)

Can you post an expression you have used in your pivot table or if possible qvw file? I suggest you also test it first in QlikView by exporting this table to Excel (sent to Excel option).

the hint i can give you is to keep NUM function outside in your expression (i think you have it inside like:

"Pick(RowNo(),

num(expre1,'##0.0'),

num(expre2,'##0.0%')

so you have to change it to something like this:

num(

Pick(RowNo(),

expre1,

expre2,)

Pick(RowNo(),

'##0.0',

'##0.0%')

)

hope this helps

regards

Lech

Re: Excel Formatting Issues

jayabedin — Thu, 09 Nov 2017 04:56:21 GMT

Hi Lech,

Thank you very much. You are in the right direction.

My syntax are: like :

Pick(Match(ActivityDescriptions,

'P1 Median Mobilisation Time',

'P1 90th Percentile Mobilisation Time',

'P1 percentage within 3 minutes',

'P2 Median Mobilisation Time',

'P2 90th Percentile Mobilisation Time'

$(vP1Mob50PercL24m),

$(vP1Mob90PercL24m),

Num($(vP13MinMobCasesL24m)/$(vP1MobCasesL24m),'###.##%'),

$(vP2Mob50PercL24m),

$(vP2Mob90PercL24m)

)

I need this right justified.

Re: Excel Formatting Issues

Lech_Miszkiewicz — Thu, 09 Nov 2017 05:34:18 GMT

so yeah,

try putting num in front of your Pick(Match... like this

num(

Pick(Match(ActivityDescriptions,

'P1 Median Mobilisation Time',

'P1 90th Percentile Mobilisation Time',

'P1 percentage within 3 minutes',

'P2 Median Mobilisation Time',

'P2 90th Percentile Mobilisation Time'

$(vP1Mob50PercL24m),

$(vP1Mob90PercL24m),

$(vP13MinMobCasesL24m)/$(vP1MobCasesL24m),

$(vP2Mob50PercL24m),

$(vP2Mob90PercL24m)

Pick(Match(ActivityDescriptions,

'P1 Median Mobilisation Time',

'P1 90th Percentile Mobilisation Time',

'P1 percentage within 3 minutes',

'P2 Median Mobilisation Time',

'P2 90th Percentile Mobilisation Time'

'###.##',

,'###.##%',

'###.##',

'###.##'

)

I know it looks silly, but i had similar issue and this was a solution....not elegant though....

let me know how it goes..

Re: Excel Formatting Issues

jayabedin — Thu, 09 Nov 2017 23:54:05 GMT

Hi Lech,

I added the NUM functioned accordingly. The result in the Qlik looks OK but once export to Excel Format changes.

Find the attachment for details please.