Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi folk,
I loaded data from excel (here attached ) using the following code:
prebudget:
CrossTable(MESE_BUDGET, VALORE_BUDGET,1)
LOAD ID_DIPENDENTE,
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10],
[11],
[12]
FROM
[..\..\..\..\Progetti\TRANSITO\AttivitàMensili\Personale\Budget.xls]
(biff, embedded labels, table is Sheet1$);
budget:
LOAD ID_DIPENDENTE,
2019 AS ANNO_COSTO,
month(MakeDate(2019,[MESE_BUDGET])) AS MESE_COSTO,
NUM(VALORE_BUDGET) AS VALORE_BUDGET
Resident prebudget;
Drop Table prebudget;
it looks ok but why I lose the mese_costo value?
ID_DIPENDENTE | ANNO_COSTO | MESE_COSTO | VALORE_BUDGET |
1 | 2019 | 1811,3400751481 | |
2 | 2019 | 2297,1878257037 | |
3 | 2019 | 2930,9870875864 | |
4 | 2019 | 3754,7310773704 | |
5 | 2019 | 1899,495 | |
6 | 2019 | 2890 | |
7 | 2019 | 2820,4402657407 | |
8 | 2019 | 3643,8581291667 | |
9 | 2019 | 3289,5 | |
10 | 2019 | 2890 | |
11 | 2019 | 3238,9850037037 | |
12 | 2019 | 3158,0103786111 | |
13 | 2019 | 2944,5648256884 | |
14 | 2019 | 3475,7810763184 | |
15 | 2019 | 1811,3400751481 | |
16 | 2019 | 1555,9022580274 | |
17 | 2019 | 3684,6656087438 | |
18 | 2019 | 3842,8017741756 | |
19 | 2019 | 2572,3333333333 | |
20 | 2019 | 3447,1407817363 | |
21 | 2019 | 0 | |
22 | 2019 | 6210,7222 | |
23 | 2019 | 2864,0102013519 | |
24 | 2019 | 4004,4422028196 | |
26 | 2019 | 2459,1370758889 | |
29 | 2019 | 2086,6763616353 | |
30 | 2019 | 3061,6884015853 | |
31 | 2019 | 3511,1032040741 | |
32 | 2019 | 1941,23 | |
33 | 2019 | 0 |
Thanks you
Try this:
num(num#(MESE_BUDGET,'##') ,'##') as MESE_BUDGET,
When you do cross table it convert the num value to text, so you have to convert them back to num format.
Try this:
LOAD ID_DIPENDENTE,
2019 AS ANNO_COSTO,
month(MakeDate(2019,num([MESE_BUDGET]))) AS MESE_COSTO,
NUM(VALORE_BUDGET) AS VALORE_BUDGET
Resident prebudget;
Hi KC,
thanks you for your reply but it still doesn't work
I tried this
// importazione dei budget
prebudget:
CrossTable(MESE_BUDGET, VALORE_BUDGET,1)
LOAD ID_DIPENDENTE,
1,
2,
3
FROM
[..\..\..\..\Mangini\Progetti\TRANSITO\AttivitàMensili\Personale\Budget.xls]
(biff, embedded labels, table is Sheet1$);
budget:
NOCONCATENATE
LOAD ID_DIPENDENTE,
2019 AS ANNO_COSTO,
MESE_BUDGET,
NUM(VALORE_BUDGET) AS VALORE_BUDGET
Resident prebudget;
it works but the problem is that MESE_BUDGET doesn't have a numerical value and when I write num(MESE_BUDGET) it return me an unkown value like before
Try this:
num(num#(MESE_BUDGET,'##') ,'##') as MESE_BUDGET,
When you do cross table it convert the num value to text, so you have to convert them back to num format.
Hi KC,
very good...this works..
thanks so much....
Best regards
VD
You are welcome 🙂