Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
));
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
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
Hi, Thanks for the reply but it didn't work
Hi,
how bout number formatting?
num(fieldname,'#,##0')
Regards,
David Sugito
load ff
from table1;
join
load ff as newff
from table2
for formatting see attachement
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
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.
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
];
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!
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