topic sum expression error in QlikView

sum expression error

Thu, 19 Apr 2012 19:18:18 GMT

Hi everyone,

i want to show you an strange issue.

I have the table 1.

I don't know why but when i use the sum expression the values are wrong (see table 2))...

The real value of sum is 51.

Can someone help me?

Thanks!

Re: sum expression error

Thu, 19 Apr 2012 19:21:50 GMT

hello

why not rename the field with no points from the script

DEAL_EXTERNAL.quantity_A as Quantity

regards

sum expression error

Thu, 19 Apr 2012 19:23:53 GMT

sorry, there was an error loading the pictures....now are ok.

Re: sum expression error

Thu, 19 Apr 2012 19:35:39 GMT

could you post an image of your table view?

Re: sum expression error

Thu, 19 Apr 2012 19:42:27 GMT

Hi Juan,

I attached two images in the main question.

Thanks

gerardo

De: Juan Fibonacci

Enviado el: jueves, 19 de abril de 2012 04:36 p.m.

Para: Pauza, Gerardo

Asunto: - Re: sum expression error

QlikCommunity <http://community.qlik.com/index.jspa>

Re: sum expression error

created by Juan Fibonacci <http://community.qlik.com/people/dragonauta> in New to QlikView - View the full discussion <http://community.qlik.com/message/212103#212103

sum expression error

Thu, 19 Apr 2012 20:43:58 GMT

i meant an image of your internal table view (CTRL + T)

Re: sum expression error

Fri, 20 Apr 2012 00:13:23 GMT

Juan, here is the table internal view

SUBSCRIPTIONS_A:

LOAD

user_id as %Key_USER_ID_A,

user_id as user_id_A

FROM (qvd);

INNER Join

load

Year(created) & Num(Month(created),'00') & Num(Day(created),'00') as %Key_CREATED_DEAL_EXTERNALS,

user_id as %Key_USER_ID_A,

user_id as user_id_A,

quantity as quantity_A,

id as deal_externals_id_A

FROM (qvd)

where `external_status` = 'A';

De: Juan Fibonacci

Enviado el: jueves, 19 de abril de 2012 05:44 p.m.

Para: Pauza, Gerardo

Asunto: - Re: sum expression error

QlikCommunity <http://community.qlik.com/index.jspa>

Re: sum expression error

created by Juan Fibonacci <http://community.qlik.com/people/dragonauta> in New to QlikView - View the full discussion <http://community.qlik.com/message/212127#212127

sum expression error

adhudson — Fri, 20 Apr 2012 00:32:08 GMT

Hi,

If it is possible, please attach the actual qvw file.

Regards

Andrew Hudson

Re: sum expression error

Fri, 20 Apr 2012 01:13:27 GMT

Hi Andrew,

I send you the qvw.

Sorry but i had to reduce the data.

Thanks for any advice

Gerardo

De: Andrew Hudson

Enviado el: jueves, 19 de abril de 2012 09:32 p.m.

Para: Pauza, Gerardo

Asunto: - Re: sum expression error

QlikCommunity <http://community.qlik.com/index.jspa>

Re: sum expression error

created by Andrew Hudson <http://community.qlik.com/people/adhudson> in New to QlikView - View the full discussion <http://community.qlik.com/message/212163#212163

Re: sum expression error

jagan — Fri, 20 Apr 2012 06:26:06 GMT

Hi,

I found that your data has duplicate values. Also Your Table has no Unique key that is why you are getting different values when displaying in table and when you are summing up. I attached your modified file with RowNo() added to a table. Hope it helps you.

Regards,

Jagan.

Re: sum expression error

Fri, 20 Apr 2012 08:31:02 GMT

Hi,

As already mentioned earlier by Jagan, your table has got duplicate keys.

I have added one chart which describes how many times your keyfield is duplicated. Total of the same is number of rows appearing in your subsription table.

Sum of the field is also justified as breakup explains the same.

Regards

Amar Nath

Re: sum expression error

Fri, 20 Apr 2012 08:59:20 GMT

Hi,

Hope attached file helps you.

Regards,

Sampath Kumar G

Re: sum expression error

Fri, 20 Apr 2012 13:46:51 GMT

Thank you very much Sampath, Amar and Jagar....

What can i do to resolve the problem of Key duplication with the inner join?

Re: sum expression error

jagan — Fri, 20 Apr 2012 14:54:42 GMT

Hi,

If you want to eliminate duplicate rows try using Distinct during load.

LOAD DISTINCT

FROM DataSource;

This will remove the duplicate rows.

If you want to keep all the rows as such

introduce a new field like RowNo(). Check file attached in above post.

Regards,

jagan.

Re: sum expression error

Fri, 20 Apr 2012 15:08:35 GMT

Thanks Jagan!

So….if i need to use inner join between two tables then i have to use a distinct load?

I mean, always inner join create duplícate keys? there is another function to use to avoid this?

De: jagan mohan

Enviado el: viernes, 20 de abril de 2012 11:55 a.m.

Para: Pauza, Gerardo

Asunto: - Re: sum expression error

QlikCommunity <http://community.qlik.com/index.jspa>

Re: sum expression error

created by jagan mohan <http://community.qlik.com/people/jagan> in New to QlikView - View the full discussion <http://community.qlik.com/message/212402#212402

Re: sum expression error

sebastiandperei — Mon, 23 Apr 2012 13:42:16 GMT

Hi Gera!!!

Usá

Sum(Aggr(Max( DEAL_EXTERNALS.quantity_A), DEAL_EXTERNALS.deal_externals_id_A))

, y podés dejar el script así como está.

El problema está en que tenés todos los valores repetidos un montón de veces. Inner Join no te duplica valores, Inner Join te quita las no coincidencias entre las tablas. Los valores duplicados podés tenerlos en cualquiera de los dos qvd que joineás.

Probalo!!

sum expression error

Mon, 23 Apr 2012 15:56:28 GMT

Sebastian,

gracias por la respuesta.

Lo que no llego a entender es porque tengo los valores duplicados, si lo unico que hago es hacer un inner join entre dos tablas con distinta informacion salvo por el user_id.

Cual seria la forma correcta de unir estas dos tablas sin generar duplicados?

Te paso el inner join que tengo ahora:

SUBSCRIPTIONS_A:

LOAD

user_id as %Key_USER_ID_A,

user_id as user_id_A

FROM [..\QVD\SUBSCRIPTIONS.QVD] (qvd);

inner Join

load

Year(created) & Num(Month(created),'00') & Num(Day(created),'00') as %Key_CREATED_DEAL_EXTERNALS,

user_id as %Key_USER_ID_A,

user_id as user_id_A,

quantity as quantity_A,

id as deal_externals_id_A

FROM [..\QVD\DEAL_EXTERNALS.QVD] (qvd)

where `external_status` = 'A';

Gracias

gerardo

sum expression error

sebastiandperei — Mon, 23 Apr 2012 16:55:26 GMT

hay que ver qué hay en los QVD...Intuyo q están en SUBSCRIPTIONS. Qué hay acá? Lo más probable es que tengas varios registros por cada user_id.

Le estás diciendo a Qlikview:

1- Leé las claves de SUBSCRIPTIONS.QVD y creá dos campos con nombres diferentes por cada una (acá es muy probable que estén repetidos)

2- Leé de DEAL_EXTERNALS.QVD tales datos, y emparentalos con cada clave leída.

Si tenés por ejemplo user_id A, A, A, B, B, C, C, C, C,... te va a poner los datos de quantity que se corresponden en la segunda tabla con A, 3 veces, con B, 2, y así....

Tenés que tener un buen conocimiento de qué hay en cada qvd antes de leerlo.

Re: sum expression error

Mon, 23 Apr 2012 19:17:31 GMT

Perfecto! Excelente explicación.

Gracias

gerardo

De: Sebastian Pereira

Enviado el: lunes, 23 de abril de 2012 01:56 p.m.

Para: Pauza, Gerardo

Asunto: - Re: sum expression error

QlikCommunity <http://community.qlik.com/index.jspa>

Re: sum expression error

created by Sebastian Pereira <http://community.qlik.com/people/sebastiandpereira> in New to QlikView - View the full discussion <http://community.qlik.com/message/212893#212893

sum expression error

sebastiandperei — Mon, 23 Apr 2012 21:39:33 GMT

Dame un Resuelto! je