Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Number Format and Cross Table

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

));

1 Solution

Accepted Solutions
er_mohit
Honored Contributor II

Re: Number Format and Cross Table

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

11 Replies
Not applicable

Re: Number Format

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

Not applicable

Re: Number Format

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

Not applicable

Re: Number Format

Hi,

how bout number formatting?

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

Regards,

David Sugito

Not applicable

Re: Number Format

load ff

from table1;

join

load ff as newff

from table2

for formatting see attachement

Not applicable

Re: Number Format

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

Not applicable

Re: Number Format

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.

Not applicable

Re: Number Format and Cross Table

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

];

Not applicable

Re: Number Format and Cross Table

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
Honored Contributor II

Re: Number Format and Cross Table

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

Community Browser