topic Re: Archive datas in QlikView

Archive datas

Mon, 17 Feb 2014 09:21:43 GMT

Hello everyone,

I'm talking to you because I'm a beginner on Qlikview and I would like to know if it's possible to archive datas to a specific date ( today for example ). To explain to you, I work with an Excel which contains datas which can be changed and we're not always able to see it because that's Excel of 66 000 lines ( I work for a huge company ), so I want to arhcive the date when the file has been changed and displays it on a dynamic cross board with the old datas with the old date and just below the new datas with the changing date.

Thanks for your help

PS : sorry for my english's level I'm french

Re: Archive datas

MK_QSL — Mon, 17 Feb 2014 10:39:36 GMT

Dear Jeremy,

You can create a QVD for old data which are not changing.

Use incremental load for new data.

Re: Archive datas

makkemik — Mon, 17 Feb 2014 10:54:34 GMT

You can have QVD tables like this;

1- First reload your data from excel and/or SQL:

TableName:

Select * from your_table;

Then You can take the date to QVD file;

Store * from TableName into TableName_Backup_Name;

This stored file will be created in your QV repor file with extention TableName_Backup_name.qvd

2- When you need the use backup file instead of SQL and/or Excel, use this load statement

Load * resident TableName_Backup_name (QVD);

I hope this detail will be enough for you. You can concatenate the excel sheets within one QVD file too.

Kind regards

Murat

Re: Archive datas

Mon, 17 Feb 2014 12:46:05 GMT

Thank you for your answers, I've already seen the function store but I don't know how to use it, and particulary after the from because I've an Excel file with multiple sheets so How to write it after the " FROM " ?

Thank you

Re: Archive datas

Mon, 17 Feb 2014 13:35:06 GMT

No in fact I find ! That's right Thank you for your help ! Have a nice day

Re: Archive datas

makkemik — Mon, 17 Feb 2014 13:35:15 GMT

Hi,

use like this;

TableName:

Load *

FROM [ExcelFile.xlsx] (ooxml, embedded labels, table is Sheet1); --excel 2010 version

FROM [ExcelFle.xls] (biff, embedded labels, table is Sheet1$); -- Excel 93-97 version old one

For multiple Sheets

TableName:

Load *

FROM [ExcelFle.xls] (biff, embedded labels, table is Sheet1$);

concatenate

Load *

FROM [ExcelFle.xls] (biff, embedded labels, table is Sheet2$);

Concatenate

Load *

FROM [ExcelFle.xls] (biff, embedded labels, table is Sheet3$);

.....

You can use like this.... if you can use PoverPivot add on in Excel you can store more than milion line in one excel sheet.

Kind regards

Murat

Re: Archive datas

penpayom — Tue, 31 Jul 2018 04:30:51 GMT

Hi, I have question

This is my code

I can store current data but if i have a new data again ,it override old data

//1. Data

Data:

LOAD Article,

Stock,

today () as date

FROM

(ooxml, embedded labels, table is Sheet1);

///////////////////////////////////////////////////////////////////////////////

2. Archive data

Archive_Data: //// 1. Load today's data

LOAD *;

IF NOT IsNull(QvdCreateTime('Data.qvd')) THEN //// 2. If a qvd file with historical data already exists then append that data to the table

CONCATENATE load(Data);

LOAD * FROM Data.qvd (qvd);

ENDIF

STORE Data INTO Archive_Data.qvd (qvd); //// 3. Store the table into a qvd file

/////////////////////////////////////////////////////////////////////////////

3. Load Archive data

Load_Archive_Data:

LOAD Article,

Stock

FROM

(qvd);