topic Re: Number Format and Cross Table in QlikView

Number Format and Cross Table

Thu, 07 Feb 2013 07:13:42 GMT

Hello All,

I'm having trouble regarding my data.

Since I have used a cross table for my data, for some reasons it won't look up at my other tables.

My field No. contains the ff:

1.000000

2.000000

etc...

My other table containts the ff:

1.000000

2.000000

etc...

basically the same fields (the primary key)

but they don't meet! How come?

Now I want to format this numbers if it will have an effect. I tried left join, still they won't meet. Any idea?

The format that I want:

1.000000 = 1

2.000000 = 2

and so on..

Thanks.

UPDATE!

It looks like that when I did the cross table, the Numbers are not numbers anymore. If I use the format num() to my field,

it returns empty!

P.S This is my cross table result:

"January":

CrossTable(No, Data, 4)

LOAD No. as Month,

F2 as Type,

F3 as Patient_Type,

F4 as H_P,

[1.000000],

[2.000000],

[3.000000],

[4.000000],

[5.000000],

[6.000000],

[7.000000],

[8.000000],

[9.000000],

[10.000000],

[11.000000],

[12.000000],

[13.000000],

[14.000000],

[15.000000],

[16.000000],

[17.000000],

[18.000000],

[19.000000],

[20.000000],

[21.000000],

[22.000000],

[23.000000],

[24.000000],

[25.000000],

[26.000000],

[27.000000],

[28.000000],

[29.000000]

FROM

C:\QlikViewData\QVD\MD\January.xlsx

(ooxml, embedded labels, table is [Regular ], filters(

Transpose(),

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null)),

Replace(3, top, StrCnd(null))

));

Re: Number Format

Thu, 07 Feb 2013 07:17:06 GMT

hi there

have u tried format those thow fields to same format?

if you havent, try this:

num(fieldName) AS newFieldName

on both table.

Hope helps

Regards,

David Sugito

Re: Number Format

Thu, 07 Feb 2013 07:24:29 GMT

Hi, Thanks for the reply but it didn't work

Re: Number Format

Thu, 07 Feb 2013 07:27:38 GMT

Hi,

how bout number formatting?

num(fieldname,'#,##0')

Regards,

David Sugito

Re: Number Format

Thu, 07 Feb 2013 07:30:55 GMT

load ff

from table1;

join

load ff as newff

from table2

for formatting see attachement

Re: Number Format

Thu, 07 Feb 2013 07:31:34 GMT

still doesn't work

I think this is something to do with my Cross Table.

What I do is I transpose my excel file then Cross Table, thing is, the primary field for the table becomes common or it is not distinct anymore since it repeats (but I think that is not the case).

this is my cross table result:

"January":

CrossTable(No, Data, 4)

LOAD No. as Month,

F2 as Type,

F3 as Patient_Type,

F4 as H_P,

[1.000000],

[2.000000],

[3.000000],

[4.000000],

[5.000000],

[6.000000],

[7.000000],

[8.000000],

[9.000000],

[10.000000],

[11.000000],

[12.000000],

[13.000000],

[14.000000],

[15.000000],

[16.000000],

[17.000000],

[18.000000],

[19.000000],

[20.000000],

[21.000000],

[22.000000],

[23.000000],

[24.000000],

[25.000000],

[26.000000],

[27.000000],

[28.000000],

[29.000000]

FROM

C:\QlikViewData\QVD\MD\January.xlsx

(ooxml, embedded labels, table is [Regular ], filters(

Transpose(),

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null)),

Replace(3, top, StrCnd(null))

));

as you can see, the fields 1-29 is supposed to be my primary key to connect to another table.

but somehow, they don't just meet. Is there any problem on how I use crosstable?

Thanks

Re: Number Format

Thu, 07 Feb 2013 07:39:41 GMT

Thanks but that doesn't work.

It looks like my field values is not considered a Number anymore but a String. (if that is correct)

based on your screenshot, even if i do overrided it, it won't apply.

Re: Number Format and Cross Table

Thu, 07 Feb 2013 07:46:20 GMT

try according to this

tab1:

LOAD Distinct * INLINE [

1.00000

2.00000

];

Concatenate

tab2:

LOAD Text(ff) as ff;

LOAD * INLINE [

1.00000

2.00000

];

Re: Number Format and Cross Table

Thu, 07 Feb 2013 07:48:37 GMT

Hi thanks for the reply.

Upon searching some solutions online.

I came up with PurgeChar() and it WORKS! 😃

PurgeChar(field_name, '.000000') as field_name

the only problem now is, 10,20,30.. and so on are missing!

Re: Number Format and Cross Table

er_mohit — Thu, 07 Feb 2013 07:59:00 GMT

try this function if you get same format

Left(F1,Index(F1,'.')-1)as num;

here F1 is my field name in both table its common

Re: Number Format and Cross Table

Thu, 07 Feb 2013 08:02:38 GMT

Thank you! That is what i'm looking for!

Re: Number Format and Cross Table

Thu, 07 Feb 2013 08:37:50 GMT

use number fixed to zero dacimal in number tab in properties.