topic Re: Problem with count of records in QlikView

Problem with count of records

Tue, 05 Jul 2011 10:29:27 GMT

Hi all,

I have a problem with rowno(), I have a connection with OLEDB, and I load a table with id, idValue and price, in a simple table I make the average of this prices, and all is correct, but my problem is that I need make the average of the last 200 records. I have seem that the function rowno(), to assign a autonumeric to each record, but when I use this function, It return the count of total table, and I need group by idValue.

Thanks in advance,

Irene.

Problem with count of records

kaushiknsolanki — Tue, 05 Jul 2011 10:32:30 GMT

Hi,

Can you please upload the script you are using.

Regards,

Kaushik Solanki

Re: Problem with count of records

Tue, 05 Jul 2011 10:39:31 GMT

Of course.

Problem with count of records

kaushiknsolanki — Tue, 05 Jul 2011 10:49:49 GMT

Hi,

From the script i think your data model may have a loop.

Can you please upload the qvw file with the o/p you want.

Regards,

Kaushik Solanki

Re: Problem with count of records

Tue, 05 Jul 2011 11:07:11 GMT

Yes, of course.

In this file you can see one operation that make the average for 200 day before, but I need make the average for 200 record before.

Thanks in advance.

Problem with count of records

kaushiknsolanki — Tue, 05 Jul 2011 11:20:14 GMT

HI,

I am not getting what you mean to say by average of 200 records before.

Please describe your problem in detail with example.

Regards,

Kaushik Solanki

Re: Problem with count of records

Tue, 05 Jul 2011 11:32:36 GMT

Yes, I have a table with this data:

id idValor close date

12 1 25 01/12/10

54 1 45 02/12/10

55 1 56 03/12/10

66 1 78 06/12/10

56 2 30 01/12/10

78 2 67 02/12/10

I need to know the average of close value, and I have the dates, but don't have records in the table for weekends and holidays, and I need make the average since the last date until 200 records befores,(NO DAYS), because only need do the avegar with working days and in the table I haven't no workings days.

group by with idValor.

I don't nkow if is more clear, sorry but my english is bad...

Re: Problem with count of records

Tue, 05 Jul 2011 13:18:21 GMT

Hi,

First you need to eliminate the circular reference from tables: "valor_indice", "valores" and "cotizaciones_valor":

valor_indice:

LOAD

IdValor & '#' & Id AS key_cotizaciones_valor,

IdIndice,

FROM your_table_A;

cotizaciones_valor:

LOAD

IdValor & '#' & Id AS keyCotizacionesValor,

IdValor AS keyValores

cierre,

Fecha,

volumen

FROM your_table_B;

valores:

LOAD

IdValor AS keyValores,

descripcionValor,

simboloValor

FROM your_table_C;

You can do a joins with this tables and input the function ROWNO() to returno the number of register.

See attachment.

Re: Problem with count of records

Tue, 05 Jul 2011 17:28:08 GMT

Hi Eduardo,

Thanks for your answer, but still don't work, the problem now, is when I launch the script, when this arrive to cotizaciones_valor the qlickviw down, and I can't do nothing.

I have attached the script, only I have changed the LOAD by SELECT.

But I don't understand well, what you want say with "delete circular references", and why drop table "mercados".

Sorry, But I'm new with qlickview 😞

Thanks for all, Irene.

Re: Problem with count of records

Tue, 05 Jul 2011 18:06:10 GMT

Hi,

I modified your script for you.

Try this

About "circular references" i want to say to delete the table "$Syn 1 Table" (the system create this table when have a "circular references"). This is a problem for a consistence of your qvw document.

Re: Problem with count of records

Tue, 05 Jul 2011 19:07:59 GMT

A lot of thanks,

But , when arrive in the script at this point:

JOIN TMP_TABLE (The firts "join").

To appear an error that say "TMP_TABLE", doesn't exist.

Irene.

Re: Problem with count of records

Tue, 05 Jul 2011 19:19:06 GMT

Hi,

Put the Function "NOCONCATENATE" after TMP_TABLE:

Ex.:

TMP_TABLE:

NOCONCATENATE

LOAD

IDMERCADO,

CODIGOMERCADO

....

Re: Problem with count of records

Tue, 05 Jul 2011 19:56:23 GMT

All is correct in the script, but when arrive at join with AUX_COTIZACIONES_VALOR, the qlickView is blocked...load the data correctly, and after doesn't make nothing... I don't nkow if the qlickview have a limit data... the table cotizaciones_valor have 2.000.000 records...

Re: Problem with count of records

Tue, 05 Jul 2011 20:09:52 GMT

Hi,

The qlikview always return all table for me.

I worked with tables more then 10.000.000 records and never blocked any register.

Maybe the joins between table are not correct. I don't know how is your model.

Do you can create a sql consult for me?

Ex.:

SELECT *

FROM VALORES, VALOR_INDICE, MERCADO...

WHERE ...

I can to try convert sql to qlikview language.

Re: Problem with count of records

Tue, 05 Jul 2011 20:29:45 GMT

Yes of course , this the model.

Re: Problem with count of records

Tue, 05 Jul 2011 20:57:32 GMT

And in the sql file you can see the query 🙂

Re: Problem with count of records

Wed, 06 Jul 2011 08:47:52 GMT

A lot of thanks for your help,

Now, it's work correctly , only I have one doubt now, if I want do the average of 200 records before, I can write this sentence:

Avg( If (numrow >= min(numrow) + 200, cierre))

Thanks in advance Irene.