topic Re: crosstable problem in QlikView

crosstable problem

vittorianad — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: crosstable problem

jyothish8807 — Wed, 10 Apr 2019 08:04:13 GMT

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;

Re: crosstable problem

vittorianad — Wed, 10 Apr 2019 08:41:21 GMT

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

Re: crosstable problem

jyothish8807 — Wed, 10 Apr 2019 09:55:31 GMT

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.

Re: crosstable problem

vittorianad — Wed, 10 Apr 2019 12:24:39 GMT

Hi KC,

very good...this works..

thanks so much....

Best regards

Re: crosstable problem

jyothish8807 — Thu, 11 Apr 2019 05:46:48 GMT

You are welcome 🙂