topic Full load and incremental load based on variable in QlikView

Full load and incremental load based on variable

sridhar_sigired — Thu, 23 Jun 2016 07:30:12 GMT

Hi,

I have a incremental code with variables.

IF $(vLoadType) = 0 THEN // FULL DATA LOAD

///Code

ELSEIF $(vLoadType) = 1 THEN // INCREAMENTAL LOAD

///Code

ENDIF;

So, whenever i want to do full load, i need to pass 0 for parameter value in QMC and incremental load need to change it to 1.

Instead of that i want to write a logic in QVW, where if particular full QVD is available in folder then, variable value should set as 1 for incremental load, otherwise it should go for full load.

Can any one help me to write a logic in generator, instead of manual parameter change at QMC?

Thanks for your help.

Regards,

Sridhar

Re: Full load and incremental load based on variable

MK_QSL — Thu, 23 Jun 2016 07:53:56 GMT

You can check whether file exists or not...

http://www.qlikviewaddict.com/2012/02/checking-file-exists.html

Command to check if a QVD file exists

Re: Full load and incremental load based on variable

sridhar_sigired — Thu, 23 Jun 2016 08:56:23 GMT

Hi,

Looks like QvdCreateTime() function is not there now, any alternated for this?

I have checked this function in help, but not available.

Re: Full load and incremental load based on variable

MK_QSL — Thu, 23 Jun 2016 09:01:56 GMT

Are you sure QvdCreateTime() is not exists now?

Re: Full load and incremental load based on variable

sridhar_sigired — Thu, 23 Jun 2016 09:07:03 GMT

I am not expert, so can you please check below...

IF (isNull(qvdCreateTime('Fact_Table.qvd'))) THEN

//Full load script

ELSEIF (NOT isNull(qvdCreateTime('Fact_Table.qvd'))) THEN

//Incremental load script

ENDIF;

Re: Full load and incremental load based on variable

MK_QSL — Thu, 23 Jun 2016 09:13:09 GMT

IF (isNull(qvdCreateTime('Fact_Table.qvd'))) THEN

//Full load script ... make sure that you are checking Fact_Table.qvd at right place... If possible try to give full path to check

// something like C:\QVDFolder\Fact_Table.qvd

ELSEIF

//Incremental load script

ENDIF;

Re: Full load and incremental load based on variable

sridhar_sigired — Thu, 23 Jun 2016 09:16:08 GMT

Yes, i dont have QVD generator and QVD in same folder.

QVDData folder - Contains QVD's

QVDGenerator folder - Contains Generator.

So, u mean I need to write below?

IF (isNull(qvdCreateTime('..\QVDData\Fact_Table.qvd'))) THEN

//Full load script ...

ELSEIF

//Incremental load script

ENDIF;

Re: Full load and incremental load based on variable

MK_QSL — Thu, 23 Jun 2016 09:17:48 GMT

You need to write Relative or FullPath according to your need... Try both for checking...

Re: Full load and incremental load based on variable

sridhar_sigired — Thu, 23 Jun 2016 09:22:46 GMT

Thanks Manish for your time, it is working with Relative path.

Re: Full load and incremental load based on variable

sridhar_sigired — Thu, 23 Jun 2016 09:48:09 GMT

Looks like it is not possible for multiple QVDs at a time? right?

Re: Full load and incremental load based on variable

MK_QSL — Thu, 23 Jun 2016 09:52:54 GMT

No... you have to do one qvd at a time..

Re: Full load and incremental load based on variable

sridhar_sigired — Thu, 23 Jun 2016 10:11:15 GMT

Unfortunately i have three facts within a generator, is there any other way we can do?

Re: Full load and incremental load based on variable

sridhar_sigired — Thu, 23 Jun 2016 10:43:41 GMT

Can anyone help me on this please ?

Re: Full load and incremental load based on variable

marcus_malinow — Thu, 23 Jun 2016 10:50:28 GMT

Sridhar,

a possible solution:

IF (isNull(qvdCreateTime('..\QVDData\Fact_Table.qvd'))) THEN

//Full load script for Fact_Table

ELSE

//Incremental load script for Fact_Table

ENDIF;

IF (isNull(qvdCreateTime('..\QVDData\Fact_Table2.qvd'))) THEN

//Full load script for Fact_Table2

ELSE

//Incremental load script for Fact_Table2

ENDIF;

Re: Full load and incremental load based on variable

chriscammers — Thu, 23 Jun 2016 17:38:18 GMT

You Could load just the xml header from the files in question and then analyze whatever conditons you need.

You'll notice a couple of functions that give you information about the file, these are read from the filesystem and you are not actually opening the file, so depending on the size of your fact tables you may want to skip reading the xml header.

Load * inline [

Foo,Bar

1,2

3,4

2,5

3,6

6,7

4,8

4,7

9,4

6,8

9,3

];

//this is an example so I'm faking the files, sleep is just making sure the create times on the file are not the same.

Store T into file1.qvd(qvd);

Sleep 10000;

Store T into file2.qvd(qvd);

Sleep 10000;

Store T into file3.qvd(qvd);

For Each file in 'file1.qvd','file2.qvd','file3.qvd';

Files:

LOAD

filetime() as FileUpdateTime,

FileName() as FileName,

FilePath() as Path,

QvBuildNo,

CreatorDoc,

CreateUtcTime,

ConvertToLocalTime(CreateUtcTime,'Central Time (US & Canada)') as LocalTime,

SourceFileSize,

TableName,

RecordByteSize,

NoOfRecords,

Offset,

Length

//%Key_QvdTableHeader_B94FCCAC68ED3E20 // Key for this table: QvdTableHeader

FROM [$(file)] (XmlSimple, Table is [QvdTableHeader]);

// End of [file1.qvd] LOAD statements

Re: Full load and incremental load based on variable

rwunderlich — Thu, 23 Jun 2016 18:46:27 GMT

Do you want to test if all three files exist, and if so do incremental logic? If only two files exist you want to do full reload for all three?

-Rob