topic Records in a table in QlikView

Records in a table

rogerioalmeida — Mon, 26 Jan 2026 18:19:17 GMT

Good afternoon everyone,
anyone have a way to decrease the amount of records in a table, which is loaded through QVD.
I'm currently using the group by already halved the amount of records they would otherwise?

Thank you.

Rogério.

Re: Records in a table

prasannarc_jbs2 — Tue, 20 May 2014 17:35:24 GMT

Hi Rogerio,

1. You can use where clause to reduce the number of records to be loaded.

2. You can split the table by normalizing the table into one or more table.

3. If it is for testing purpose You can use below syntax,

<Table Name>:

First <Num>

LOAD

<Column1>,

<Column2>,

From <Table Name>.qvd;

P.S. Replace the data inside <> with your Filed name.

eg.

SALES_FACT:

First 1000

Load

Product,

Brand,

Sum(Sales) as Sales

From xyz.qvd

Group By Product, Brand;

Regards,

Prasanna

Re: Records in a table

Tue, 20 May 2014 17:36:08 GMT

Have you tried using a WHERE clause?

🙂

Re: Records in a table

Tue, 20 May 2014 17:38:30 GMT

Please look at this link: http://www.qlikcommunity.com/thread/62205

Re: Records in a table

Colin-Albert — Tue, 20 May 2014 17:44:42 GMT

Can you give more information on why you want to reduce the number of records loaded?

More information on your requirements will help you get an answer that is relevant.

Re: Records in a table

rogerioalmeida — Tue, 20 May 2014 17:50:33 GMT

I am using the following script, I hope you understand.

LANCAMENTO_CONTABIL:

LOAD

CLAPAI3,

if(CTAPAIANT = IsNull(CTAPAIANT), CONTA_CONTABIL, CTAPAIANT) as CTAPAIANT, //CTAPAIANT,

if(CLAPAIANT = IsNull(CLAPAIANT), CLASSIFICACAO,CLAPAIANT) as CLAPAIANT,

CLAPAI4,

CLAPAI1,

CLAPAI2,

SUM(if (TIPO_LANCAMENTO = 'D',(VALOR_LANCAMENTO), if (TIPO_LANCAMENTO = 'C',(VALOR_LANCAMENTO*(-1)) ))) as VALOR_LANCAMENTO

FROM $(vPath_QVD)\LANCAMENTO_CONTABIL.qvd (qvd)

WHERE YEAR(DATA_LANCAMENTO) >= '2011'

GROUP BY

CLAPAI3,

if(CTAPAIANT = IsNull(CTAPAIANT),

CONTA_CONTABIL, CTAPAIANT),

if(CLAPAIANT = IsNull(CLAPAIANT),

CLASSIFICACAO,CLAPAIANT),

CLAPAI4,

CLAPAI1,

CLAPAI2

;

With this script in my load I've got 13 million records, had 26 million before and is not acceptable.

Re: Records in a table

Colin-Albert — Tue, 20 May 2014 18:19:01 GMT

Don't consider the number of records, the issue to look at is the number of unique records and the time taken to process the data. I guess your Group By clause is slow as QlikView is having to create the unique set of values in memory to process the group before summing the data.

First I would move the two IF statements into your first QVD file LANCAMENTO_CONTABIL.qvd

if(CTAPAIANT = IsNull(CTAPAIANT), CONTA_CONTABIL, CTAPAIANT) as CLAPAI5

if(CLAPAIANT = IsNull(CLAPAIANT), CLASSIFICACAO, CLAPAIANT) as CLAPAI6

Then add a compound key CLAPAI3 & CLAPAI5 & CLAPAI6 & CLAPAI4 & CLAPAI1 & CLAPAI2

This means that the generation of the unique values only happens once when the QVD is created, and the grouping will not have any IF clauses.

Re: Records in a table

Colin-Albert — Tue, 20 May 2014 18:24:57 GMT

Also add the +1 -1 multiplier for C or D into your first QVD file

if (TIPO_LANCAMENTO = 'C', -1, 1) as FACTOR

Then you can imply use SUM ( VALOR_LANCAMENTO * FACTOR) as VALOR_LANCAMENTO later in your script