topic Re: Cross table in QlikView

Cross table

Mon, 11 Nov 2013 21:06:29 GMT

Hi everyone;

I charge data BUT when I use "Crosstable" I don't now why is not correct the charge for example

In the file Excel called "acumulados" I filter the information by column "INFORMACION" and Choose:

ACREEDORES, COSTO INTEGRAL,CXC,CXP, DEUDORES, GASTO VENTA. and then I sum all columns for all months the columns to SUM is 201301,201302...201312

the result is = 33797698

and this is correct...BUT!!!!

AFTER I Charge the data from excel file apply Crosstable and the column INFORMATION apply

IF(INFORMACION='VENTA',201,IF(INFORMACION='COSTO',57,IF( INFORMACION='GASTO DIRECCION','XXX',IF(INFORMACION='GASTO ADMON', 'YYY',IF (INFORMACION='COSTO VENTA','ZZZ','BALANCE' ) ) ) ) ) AS INFORMACION,

and after I filter the information in the column called "INFORMACION" again and Choose now: BALANCE and then sum the column Data = 33761201

and this is NOT correct...

BALANCE = sum of the ACREEDORES, COSTO INTEGRAL,CXC,CXP, DEUDORES, GASTO VENTA.

Can you help please?

CrossTable (test, Data, 3)
LOAD
     IF(INFORMACION='VENTA',201,IF(INFORMACION='COSTO',57,IF( INFORMACION='GASTO DIRECCION','XXX',IF(INFORMACION='GASTO ADMON', 'YYY',IF (INFORMACION='COSTO VENTA','ZZZ','BALANCE' ) ) ) ) ) AS INFORMACION,
     VENDEDORA,
     COMPRADORA,
     [201301],
     [201302],
     [201303],
     [201304],
     [201305],
     [201306],
     [201307],
     [201308],
     [201309],
     [201310],
     [201311],
     [201312]
FROM
C:\Users\olimon\Documents\acumulados.xls
(biff, embedded labels, table is [Sheet1$]);

Re: Cross table

hectorgarcia — Mon, 11 Nov 2013 21:36:09 GMT

Omar I sugest you to do the transformations to your table after you have created the table with the crosstable,

that way you can simplify your script.

It is one step more but you can do it in a more structured way.

espero haberte podido ayudar

Hector

Re: Cross table

Mon, 11 Nov 2013 21:40:49 GMT

I understand you what you mean is that FIRST charge the data in a resident table and after use the CorssTable function. oohhh I gee but sorry dosen't work in my originally .qvw I have this form what you say. but thanks anyway!!!!1 for your help!!!

another idea?

Re: Cross table

hectorgarcia — Mon, 11 Nov 2013 21:48:51 GMT

aplica este script y llegaras a este resultado:

data:

CrossTable(Fechas, Datos, 3)

LOAD INFORMACION,

VENDEDORA,

COMPRADORA,

[201301],

[201302],

[201303],

[201304],

[201305],

[201306],

[201307],

[201308],

[201309],

[201310],

[201311],

[201312]

FROM

acumulados.xls

(biff, embedded labels, table is [Sheet1$]);

data1:

Load

resident data;

drop table data;

drop Field INFORMACION;

rename field INFORMACION1 to INFORMACION;

exit script;

Re: Cross table

Mon, 11 Nov 2013 22:09:02 GMT

Carlos tienes razon estaba usando una table Box para cuadrar el dato pero pues nunca iba a salir, ya use la straight table y si efectivamente salio el dato, muchisimas gracias por tu apoyo.

saludos!!!!