topic How to store Qvd dynamically? in QlikView

How to store Qvd dynamically?

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

i want to store the Qvd dynamically

how can i achieve this?

can anyone give me sample example?

Thanks

Sony

Re: How to store Qvd dynamically?

dsharmaqv — Wed, 01 Aug 2018 09:51:17 GMT

what do you mean by dynamically? pls elaborate

Re: How to store Qvd dynamically?

soniasweety — Wed, 01 Aug 2018 09:51:59 GMT

dynamic path i need to store

Re: How to store Qvd dynamically?

BalaBhaskar_Qlik — Wed, 01 Aug 2018 09:52:07 GMT

Use like these: 4 different scenarios.

For i = 2015 to Year(Today())

ABC:

Load * Inline [

];

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

DROP Table ABC;

NEXT i;

Let i = Year(Today());

ABC:

Load * Inline [

];

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

DROP Table ABC;

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];

//Drop tables

LET j = NoOfTables();

DO WHILE j > 1

LET TableDrop = TableName(0);

DROP TABLE [$(TableDrop)];

LET j = NoOfTables();

LOOP

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

Re: How to store Qvd dynamically?

soniasweety — Wed, 01 Aug 2018 09:53:54 GMT

thanks bala very helpful.

Re: How to store Qvd dynamically?

soniasweety — Wed, 01 Aug 2018 09:56:00 GMT

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?

Re: How to store Qvd dynamically?

BalaBhaskar_Qlik — Wed, 01 Aug 2018 10:06:57 GMT

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);

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

Re: How to store Qvd dynamically?

soniasweety — Wed, 01 Aug 2018 10:10:47 GMT

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

Re: How to store Qvd dynamically?

BalaBhaskar_Qlik — Wed, 01 Aug 2018 10:19:32 GMT

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

Re: How to store Qvd dynamically?

soniasweety — Wed, 01 Aug 2018 10:22:52 GMT

sure i will be doing that thanks

Re: How to store Qvd dynamically?

soniasweety — Wed, 01 Aug 2018 11:57:47 GMT

i have tried the code but i didnt get any thing

balabhaskarqlik

Re: How to store Qvd dynamically?

soniasweety — Thu, 02 Aug 2018 13:34:07 GMT

Hi balabhaskarqlik

any update on this?