Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

crosstable problem

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_DIPENDENTEANNO_COSTOMESE_COSTOVALORE_BUDGET
12019 1811,3400751481
22019 2297,1878257037
32019 2930,9870875864
42019 3754,7310773704
52019 1899,495
62019 2890
72019 2820,4402657407
82019 3643,8581291667
92019 3289,5
102019 2890
112019 3238,9850037037
122019 3158,0103786111
132019 2944,5648256884
142019 3475,7810763184
152019 1811,3400751481
162019 1555,9022580274
172019 3684,6656087438
182019 3842,8017741756
192019 2572,3333333333
202019 3447,1407817363
212019 0
222019 6210,7222
232019 2864,0102013519
242019 4004,4422028196
262019 2459,1370758889
292019 2086,6763616353
302019 3061,6884015853
312019 3511,1032040741
322019 1941,23
332019 0

 

Thanks you

1 Solution

Accepted Solutions
Highlighted
Master II
Master II

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.

Best Regards,
KC

View solution in original post

5 Replies
Highlighted
Master II
Master II

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;

Best Regards,
KC
Highlighted
Contributor III
Contributor III

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

Highlighted
Master II
Master II

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.

Best Regards,
KC

View solution in original post

Highlighted
Contributor III
Contributor III

Hi KC,

very good...this works..

thanks so much....

Best regards

VD

Highlighted
Master II
Master II

You are welcome 🙂

Best Regards,
KC