topic Creating monthly qvds that doesn't exists in QlikView

Creating monthly qvds that doesn't exists

qlikviewforum — Thu, 08 Sep 2016 10:49:09 GMT

I have monthly so many monthly qvds like below. All these qvds were created based on the csv files.

EMPLOYEE.2016_01.qvd
EMPLOYEE.2016_02.qvd
EMPLOYEE.2016_02.qvd
.
.
.
EMPLOYEE.2016_07.qvd

Now we receives the data in Database table and it has field called YR_MNTH(201608). As the data will be from DB table I need to create only those monthly qvd's which doesn't exists already.

Can someone help on this.

Re: Creating monthly qvds that doesn't exists

marcus_sommer — Thu, 08 Sep 2016 11:07:10 GMT

To ensure the data-consistency to the database I would create each qvd-slice new. For this you could use a logic like here: Year Wise Qvd creation from Source Data. You need only to change the year to your YearMonth. If really want to add the missing slices you could use a quite similar logic and skip the loading or maybe the storing of the qvd - maybe something like:

...

if filesize(QVDFilenameVariable) > 0 then

// do nothing

else

store QVDFilenameVariable into QVDFilenameVariable.qvd (qvd);

end if

...

- Marcus

Re: Creating monthly qvds that doesn't exists

chetansehgal — Thu, 08 Sep 2016 11:10:01 GMT

Hi Rikab,

You can use the incremental load approach here.

First you need to find the max date from data which is already loaded in qvw file.

Afterward you have to load only those records from database whose date >max(date in qvw).

Temp:

Load

max(YR_MNTH) as MaxDate from existing_data_set;

let vmaxdate= Num(Peek('MaxDate', 0, 'Temp'));

Load *

from table

Where

Date(YR_MNTH) > vmaxdate;

Thanks,

Chetan

Re: Creating monthly qvds that doesn't exists

qlikviewforum — Thu, 08 Sep 2016 13:27:53 GMT

Thank you Marcus!

Any other alternative guys please?

Re: Creating monthly qvds that doesn't exists

qlikviewforum — Mon, 12 Sep 2016 06:07:32 GMT

Thanks but when we data for two months then this wont workout right? Can you please suggest someother method.

I thought of below one.

1. Find all the distinct months loaded so far based on the existing QVD's
2. Find out distinct months that need to be created qvd based on the data available in database using where not exists. May be comma seperated dates(2016_08,2016_09).
3. Use a loop to create only those qvd's that doesn't exists. I this case it will generate only 2016_08 and 2016_09 which doesn't exists.

Got the idea but struggling to start with.