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,
From <Table Name>.qvd;
P.S. Replace the data inside <> with your Filed name.
Sum(Sales) as Sales
Group By Product, Brand;
I am using the following script, I hope you understand.
if(CTAPAIANT = IsNull(CTAPAIANT), CONTA_CONTABIL, CTAPAIANT) as CTAPAIANT, //CTAPAIANT,
if(CLAPAIANT = IsNull(CLAPAIANT), CLASSIFICACAO,CLAPAIANT) as CLAPAIANT,
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'
if(CTAPAIANT = IsNull(CTAPAIANT),
if(CLAPAIANT = IsNull(CLAPAIANT),
With this script in my load I've got 13 million records, had 26 million before and is not acceptable.
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.