Formating feilds types in a crosstable

[richard_pearce6]

Please can anyone advise,

I'm testing a way to format fields I've previously loaded into a cross table, so there are many different types (number, text, date, etc). I'm using a reference field within an If statement. They are all originally loaded as text

Data:

NoConcatenate LOAD

Ref

,Field_Name

,Data_Type

,Value as Original_Value



// Deal with formating

,if(Data_Type='NumD0',num(Num#(Value),'#,##0;(#,##0)') // Converts to Number, Adds 0 Decimal Place, Thousand separator and puts negative numbers in brackets

,if(Data_Type='NumD1',num(Num#(Value),'#,##0.0;(#,##0.0)') // Converts to Number, Adds 1 Decimal Place, Thousand separator and puts negative numbers in brackets

,if(Data_Type='NumD2',num(Num#(Value),'#,##0.00;(#,##0.00)') // Converts to Number, Adds 2 Decimal Place, Thousand separator and puts negative numbers in brackets                    

,if(Data_Type='Date',Date(num#(Value),'DD-MMM-YYYY') // Converts to Date

,if(Data_Type='Timestamp',Timestamp(num#(Value),'DD-MMM-YYYY hh:mmtt') // Coverts to Timestamp

,if(Data_Type='Time',Date(num#(Value),'hh:mmtt') // Converts to Time

,Value // Text String (Do Nothing)

)))))) as Value



Resident Data_Temp2;

NumD0 works, as does Date, Timestamp and Time.

Interestingly though NumD1 and NumD2 doesn't work and instead it defaults to the format set for NumD0. If I change the format there they will all change to match (i.e. 0,2 or 2 decimal places)

I'm not sure if this is to do with my code or if QV is making an assumption based on what has gone before? Any ideas? Thanks