num()? how to change this one?

[#1]

Hello all

Need some help.

table1:
CrossTable(number, cost)
load f1 as 1
 f2 as 2
 f3 as 2
 .......
from ***************************

left join(type)
load num(number) as numbers
 ,cost
resident table1;

this num() didn't work. Why?
is that because of crosstable? What should i do?

[Miguel_Angel_Baeyens]

Hello,

My guess is that the original table has some values in fields f1, f2, f3, etc. that are not interpretable as numeric, for some reason. What you are doing in your crosstable load script is rename your field "f1" to "1" instead of actually assigning the value "1" to field "f1". Is that possible? Is that the expected behavior?

Try for testing

Table1Temp:LOAD Num(f1) AS f1FROM Table....

As see if the values are formatted properly.

Hope this helps.

[#3]

Hello Miguel

i have 2 tables.
table1: Table2:
+++++++++++++++++++++ +++++++++++++++++++++++++++++++
+country +zone + +weight +f1 +f2 +f3 +
+AB +2 + +0.5 +6 +8 +10 +
+BC +5 + +1 +8 +10 +12 +
+DG +4 + +1.5 +11 +13 +15 +
+++++++++++++++++++++ +++++++++++++++++++++++++++++++


I used crosstable to change table2 like this.

+++++++++++++++++++++
+weight +zone +cost +
+0.5 +1 +6 +
+1 +1 +8 +
+1.5 +1 +10 +
+++++++++++++++++++++

That's why i need to change f1 to 1. And now its in string so i need to change it to number.

[Miguel_Angel_Baeyens]

Hello,

Taking advantage of one previous posted answer:

FieldLabelsMap:MAPPING LOAD * INLINE [ LABEL_NAME, LABEL_TEXT f1, 1 f2, 2 f3, 3]; table1: LOAD * INLINE [country, zoneAB, 2BC, 5DG, 4]; Table2:LOAD * INLINE [weight, f1, f2, f30.5, 6, 8, 10 1, 8, 10, 12 1.5, 11, 13, 15 ]; RENAME FIELDS USING FieldLabelsMap; // Check the table above, change as needed TableCrossed:CROSSTABLE (zone, cost) LOAD *RESIDENT Table2;

Hope that helps.

[#5]

Well i tried your script. Point is i need to change text to number in zone field. But its still text in zone field. i tried num(zone) but its gave me null.
Sorry im new in QV. Is there any magical functions or trick to do this. num() didn't work this one.

[Miguel_Angel_Baeyens · Accepted solution]

You may need the Num#() function. Try the following piece of script after the one posted above:

DROP TABLE Table2; TableNum:NOCONCATENATE LOAD weight, Num(Num#(zone)) AS zone, // This "forces" to interpret field "zone" as numeric Num(cost) AS costRESIDENT TableCrossed; DROP TABLE TableCrossed;

Hope that helps.

[#7]

Thank you Miguel.

you're the best.