Formating feilds types in a crosstable

richard_pearce6 — Thu, 08 Aug 2013 14:29:45 GMT

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

topic Formating feilds types in a crosstable in QlikView

Formating feilds types in a crosstable