Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Cross table

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!!!!

sumatotal.jpg

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$]);

1 Solution

Accepted Solutions
hectorgarcia
Contributor III

Re: Cross table

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;

4 Replies
hectorgarcia
Contributor III

Re: Cross table

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

Not applicable

Re: Cross table

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?

hectorgarcia
Contributor III

Re: Cross table

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;

Not applicable

Re: Cross table

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!!!!

Community Browser