Number Format and Cross Table

[#1]

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

));

[#2]

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

[#3]

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

[#4]

Hi,

how bout number formatting?

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

Regards,

David Sugito

[#5]

load ff

from table1;

join

load ff as newff

from table2

for formatting see attachement

[#6]

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

[#7]

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.

[#8]

hi

try according to this

tab1:

LOAD Distinct * INLINE [

    ff

    1.00000

    2.00000

];

Concatenate

tab2:

LOAD Text(ff) as ff;

LOAD   * INLINE [

    ff

    1.00000

    2.00000

];

[#9]

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!

[er_mohit · Accepted solution]

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