topic Different Format on Different Rows in QlikView

Different Format on Different Rows

pgalvezt — Tue, 06 Dec 2011 19:16:26 GMT

Hello!

In my excel report I have one colum A with 6 rows results. Each Row contains a different format, for example, numeric format, % format, Date format, money format. Its possible to do that in QlikView.

Thanks!

Different Format on Different Rows

IAMDV — Tue, 06 Dec 2011 21:42:03 GMT

Hi,

I am not sure if I have fully understood the question. Do you want to load this data from Excel to QlikView or do you want to create similar data? You can do transformation within QV to clean the data. There are many functions and you can use Peek() & Previous() record based functions to clean up your data. Please can you explain in detail or post an example for more understanding.

Thanks - DV

Re: Different Format on Different Rows

johnw — Tue, 06 Dec 2011 23:05:45 GMT

Well, I don't know the conditions for your rows, but it's certainly possible. For instance, something like this:

num(sum(Amount),if(Amount>100,'#,##0','#,##0.00'))

String some ifs together or use pick(match()) or whatever you want to select the right format based on your conditions.

Re: Different Format on Different Rows

pgalvezt — Tue, 06 Dec 2011 23:12:48 GMT

Hello,

Thanks for your replies,

What I want to do is load the data excel to QlikView and change the format rows. I Attached the excel example.

Re: Different Format on Different Rows

johnw — Tue, 06 Dec 2011 23:22:18 GMT

OK, it appears that you have a column (though you haven't named it) that indicates the data type. I'd associate an actual format with each of these in a table in the script, such as:

[Data Types]:
LOAD * INLINE [
Data Type: Format
Numeric: #,##0
Percentage: #,##0.00%
Money: $#,##0.00;($#,##0.00)
] (delimiter is ':');

And then do something like this:

num(sum(Amount),Format)

Re: Different Format on Different Rows

pgalvezt — Wed, 07 Dec 2011 00:09:27 GMT

Thanks, but doesn´t work. No changes.

Re: Different Format on Different Rows

pgalvezt — Wed, 07 Dec 2011 00:29:19 GMT

Any more ideas?

Re: Different Format on Different Rows

Sokkorn — Wed, 07 Dec 2011 01:15:00 GMT

Hi pgalvezt,

Let try with my attached files.

You should use with my attached excel file to view the result. For your original excel have some problem with one row <Porcentage> and I change it to <Percentage>.

HTH and let me know.

Regards,

Sokkorn Cheav

Different Format on Different Rows

Wed, 21 Mar 2012 15:34:12 GMT

John Witherspoon's solution works if you specify "Predefined Expression" in the Number Tab of Properties window. If you force any other format on that tab, it overwrites Num(FIELD, Format).