topic Table doesn't save number format in Connectivity & Data Prep

Table doesn't save number format

pedrohenriqueperna — Tue, 27 Feb 2024 19:25:25 GMT

Hi,

I have a script where I format some numbers before saving the table into a .qvd. When debugging the script and looking at the table in the data model, the formatting is correct, but after saving the table it doesn't match.

The original table contains raw data that is displayed as e.g 10.99000. I format the number using Num(price, '###0.00') (not sure if it's the correct, but it display exactly what I need, I just tweaked it until I got it right)

The output is 10.99. When I open the .qvd the price is saved just like the original raw data table: 10.99000

Any ideas?

Re: Table doesn't save number format

BrunPierre — Tue, 27 Feb 2024 20:19:07 GMT

Try

Num(Num#("10.99000", '#,##0.00000'), '#,##0.00')

Re: Table doesn't save number format

pedrohenriqueperna — Wed, 28 Feb 2024 14:28:30 GMT

Hi,

Thanks for the reply.

Unfortunately it didn't work...

I've been tweaking it around trying to make it work and I noticed something really strange.

If I format the field with:

Num(price, '#,00') as price

...nothing happens. The value is not formatted at all. But if I save the field with a different name, lets say:

Num(price, '#,00') as price_new

It works as expected!!

What is going on? Is this a bug?

Re: Table doesn't save number format

BrunPierre — Wed, 28 Feb 2024 17:18:26 GMT

Ensure that there are no other fields in your data model with the same name as "price."

Provide the script and sample data for a clearer understanding.

Re: Table doesn't save number format

pedrohenriqueperna — Wed, 28 Feb 2024 17:22:11 GMT

I found a post that gave me an insight. Num() function treats the field as a dual value, so saving it with the same name will persist the original formatting.

I managed to fix my problem by saving it as a new field, dropping the old one and renaming the new field to the previous name: price as price_new > rename price_new to price.

Thanks for your help though.