Incremental Storing in QVD

[#1]

Hello,

I work with a huge amount of data and i would like to store them into QVD splitted per month.
I've tried to manage it:

SALES:
LOAD Country,
 Business,
 Date,
 Month(Date) as Month,
 Sales
FROM [BigFile.xls];

DATES:
LOAD DISTINCT Month
RESIDENT Sales;
LET vCurrentMonth = peek('...')
SALES_$(vCurrentMonth):
LOAD *
RESIDENT SALES
WHERE....

Has anybody managed to store incrementally QVD?

Thanks a lot for your help 🙂

[fernandotoledo]

Normaly we use only one qvd, the result is quite the same, but the order for loading historical data and incremental data.

//SETS THE PERIOD PARAMETER
LET THIS_MONTH = MONTHSTART(TODAY(),0);
LET THIS_MONTH_NAME = MONTHNAME(MONTHSTART(TODAY(),0));

//CREATES THE INCREMENT
TABLE:
SQL SELECT A, B, C FROM BIG TABLE
WHERE DATE >= $(THIS_MONTH);

STORE TABLE INTO TABLE_$(THIS_MONTH_NAME).QVD;

//LOADS HISTORICAL DATA FROM QVD FILES

TABLE_HISTORICAL_CONTENT:
LOAD A, B, C FROM TABLE_*.QVD (QVD);

hope it gives an idea!

Fernando

[#3]

I'm using this custom made function:

SUB SplitTable(tableName, filePrefix, partitionCondition)

__TMP_PARTITIONS_A:

NOCONCATENATE

LOAD DISTINCT

$(partitionCondition) AS PartitionId

RESIDENT $(tableName);

__TMP_PARTITIONS:

NOCONCATENATE

LOAD PartitionId

RESIDENT __TMP_PARTITIONS_A

ORDER BY PartitionId;

DROP TABLE __TMP_PARTITIONS_A;

LET partitions = NoOfRows('__TMP_PARTITIONS');

FOR i=1 TO $(partitions)

LET currentPartition = Peek('PartitionId', $(i)-1, '__TMP_PARTITIONS');

__TMP_CurrentPartitionData:

NOCONCATENATE

LOAD *

RESIDENT $(tableName)

WHERE $(partitionCondition) = '$(currentPartition)';

STORE __TMP_CurrentPartitionData INTO '$(filePrefix)_$(currentPartition).qvd';

DROP TABLE __TMP_CurrentPartitionData;

NEXT i

// Valores nulos

__TMP_CurrentPartitionData:

NOCONCATENATE

LOAD *

RESIDENT $(tableName)

WHERE IsNull($(partitionCondition));

STORE __TMP_CurrentPartitionData INTO '$(filePrefix)_NULL.qvd';

DROP TABLE __TMP_CurrentPartitionData;

DROP TABLE __TMP_PARTITIONS;

END SUB

Call example:

CALL SplitTable('Transactions', '$(VL2_FILES)\L2-Transactions', '[YearMonth]');

dd. //

[qlikviewforum]

I am looking for something similar. Can you please help me out with sample app.

http://community.qlik.com/message/321197#321197