Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

soniasweety
Valued Contributor III

How to store Qvd dynamically?

Hi all,

i want to store the Qvd dynamically

how can i achieve this?

can anyone give me sample example?

Thanks

Sony

Tags (2)
1 Solution

Accepted Solutions
balabhaskarqlik
Honored Contributor

Re: How to store Qvd dynamically?

Like this:

To identify different sheets loaded:

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

Table:

Load * ,

FileBaseName() as FIle,

NoofRecords() as Records

FileDir() as Dir,

FileName() as File_Name,

'$(sheetName)' as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

To check load time:

LET vStart = Now(1);

load Rand() AutoGenerate 3000000;

LET vElapsed = interval(now(1) - '$(vStart)');

TRACE $(vElapsed);

OR

Calculate script run time instead to find the out we need to call subroutines

  

// Declare the variables

SET vStartTime=;

SET vEndTime=;

SET vTimeTake=;

// Start Load time

// Call before load time

Sub StartLoad

    Let vStartTime = num(now());

End Sub;

 

// Sub to calculate time in Load

// Sub timeCalculate(vStartTime,vEndTime,vTableName,vMessage)

Sub timeCalculate(vTableName,vMessage)

     // Calculate the End Time

     Let vEndTime = num(now());

     // Initlize the time taken

     SET vTimeTake=;

     // Calculate the time Taken

     LET vTimeTake = Date(($(vEndTime) - $(vStartTime)),'hh:mm:ss');

         // LOG Table Writing

     Log:

     Load

         now(1) as TimeStamp,   

         '$(vTableName)' as TableName,   

         '$(vTimeTake)'  as LoadTime,

         '$(vMessage)'  as LogMessage

     AutoGenerate 1;  

     SET vStartTime=;

     SET vEndTime=;

     SET vTimeTake=;

End Sub;

Sub LogWrite

       // Store and Reload Log File

    Store Log into log.txt (txt);

    DROP Table Log;

       End Sub

11 Replies
dsharmaqv
Contributor III

Re: How to store Qvd dynamically?

what do you mean by dynamically? pls elaborate

soniasweety
Valued Contributor III

Re: How to store Qvd dynamically?

dynamic path  i need to store  

Highlighted
balabhaskarqlik
Honored Contributor

Re: How to store Qvd dynamically?

Use like these: 4 different scenarios.

1)

For i = 2015 to Year(Today())

ABC:

Load * Inline [

A

1

2

3

4

5

];

Store ABC into ABC_$(i).qvd(qvd);

DROP Table ABC;

NEXT i;

2)

Let i = Year(Today());

ABC:

Load * Inline [

A

1

2

3

4

5

];

Store ABC into ABC_$(i).qvd(qvd);

DROP Table ABC;

3)

Set vQVD_Folder =  'some_folder' //put here the directory where you want to store your qvds

//Store QVDs

FOR i = 0 TO NoOfTables() - 1

  LET Table = TableName(i);

  STORE [$(Table)] INTO [$(vQVD_Folder)\$(Table).qvd];

NEXT

//Drop tables

LET j = NoOfTables();

DO WHILE j > 1

  LET TableDrop = TableName(0);

  DROP TABLE [$(TableDrop)];

  LET j = NoOfTables();

LOOP

4)

Let vFromDate=num(MonthStart(addmonths(now(), -13)));

Let vToDate = num(floor(now(),1)) - 4;

do while vFromDate < vToDate

// logic

Let QVDFileName = '..\qvd\Data' & '-' & date(vFromDate, 'YYYY-MMM-D') & '.qvd';

store Data into $(QVDFileName );

Drop table Data;

Let vFromDate = vFromDate + 1;

loop

soniasweety
Valued Contributor III

Re: How to store Qvd dynamically?

thanks bala very helpful.

soniasweety
Valued Contributor III

Re: How to store Qvd dynamically?

i want to know.  in front end

like below

Number of rows to each table i loaded ?    distinct rows and  with duplicate ?

Load time i want to see in text object ?---  means how much time it has taken to reload the qvw ?

number of tables loaded in  qvw ?     how can i check number of tables loaded ?

is there any way to display the same?

balabhaskarqlik
Honored Contributor

Re: How to store Qvd dynamically?

Like this:

To identify different sheets loaded:

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

Table:

Load * ,

FileBaseName() as FIle,

NoofRecords() as Records

FileDir() as Dir,

FileName() as File_Name,

'$(sheetName)' as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

To check load time:

LET vStart = Now(1);

load Rand() AutoGenerate 3000000;

LET vElapsed = interval(now(1) - '$(vStart)');

TRACE $(vElapsed);

OR

Calculate script run time instead to find the out we need to call subroutines

  

// Declare the variables

SET vStartTime=;

SET vEndTime=;

SET vTimeTake=;

// Start Load time

// Call before load time

Sub StartLoad

    Let vStartTime = num(now());

End Sub;

 

// Sub to calculate time in Load

// Sub timeCalculate(vStartTime,vEndTime,vTableName,vMessage)

Sub timeCalculate(vTableName,vMessage)

     // Calculate the End Time

     Let vEndTime = num(now());

     // Initlize the time taken

     SET vTimeTake=;

     // Calculate the time Taken

     LET vTimeTake = Date(($(vEndTime) - $(vStartTime)),'hh:mm:ss');

         // LOG Table Writing

     Log:

     Load

         now(1) as TimeStamp,   

         '$(vTableName)' as TableName,   

         '$(vTimeTake)'  as LoadTime,

         '$(vMessage)'  as LogMessage

     AutoGenerate 1;  

     SET vStartTime=;

     SET vEndTime=;

     SET vTimeTake=;

End Sub;

Sub LogWrite

       // Store and Reload Log File

    Store Log into log.txt (txt);

    DROP Table Log;

       End Sub

soniasweety
Valued Contributor III

Re: How to store Qvd dynamically?

thanks alot for nice info     if you dont mind could please attach one sample for this?    with some file

balabhaskarqlik
Honored Contributor

Re: How to store Qvd dynamically?

I'll try for that, meanwhile you can also try with some functionalities from the above mentioned.

soniasweety
Valued Contributor III

Re: How to store Qvd dynamically?

sure i will be doing that      thanks