Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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$]);
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
*,
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 INFORMACION1
resident data;
drop table data;
drop Field INFORMACION;
rename field INFORMACION1 to INFORMACION;
exit script;
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
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?
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
*,
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 INFORMACION1
resident data;
drop table data;
drop Field INFORMACION;
rename field INFORMACION1 to INFORMACION;
exit script;
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!!!!