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