Different Format on Different Rows

[pgalvezt]

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!

[IAMDV]

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

[johnw]

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.

[pgalvezt]

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. 

[johnw]

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)

[pgalvezt]

Thanks, but doesn´t work. No changes.

[pgalvezt]

Any more ideas?

[Sokkorn]

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

[#9]

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).