Dynamic qvd store and load from teradata

[Wayne_101]

Hi , 

i need help in creating dynamic qvd files for each date and wants to delete the oldest date qvd file storing the new one with date appended also would like to load it incrementally.

my source is teradata and i have one big table containing 15 days of data, days should remain the same.

Solution i tried and way forward ? :

i have stored 15 qvd files manually using simple store statement having dates from 2019/10/25 to 2019/11/10 and now i wish to automate the process like storing the data of 2019/11/11 into a new qvd file and deleting the qvd of 2019/10/25. Keeping the set of 15 qvds of 15 days data.

same goes For loading process i would like to drop oldest date data and load new date data from its qvd

note:(i have made different apps for storing and loading

[treysmithdev]

Are you looking for Qlik to do the removal of old QVDs? If so, you would end up needing to open up the security using Legacy mode and use Qlik to either start an external script or execute a terminal command from the load script. I would advise not doing that. I would suggest doing a consistent naming scheme and overwrite the files. Eg. Table_01.qvd, Table_02.qvd, Table_03.qvd, etc...

If so, then something like this should work. 

Let vStartDate = Date(Today(),'YYYY/MM/DD');


For d = 0 to 14

    Let vDate = Date($(vStartDate)-$(d),'YYYY/MM/DD');

    Table:
    SQL SELECT * FROM table WHERE Date = '$(vDate)';


    Let vSuffix = Text(Num($(d)+1,'00'));
    Store Table into [lib://QVD/Table_$(vSuffix).qvd](qvd);

    Drop Table Table;

Next d;

[Wayne_101]

hi,

i have implemented your script but facing below issues :

1) vDate gives wrong format due to which wrong data is being fetched.

2) i want to append the date that file has with it but it gives me eg: Table_562.333,Table_2019,Table_2091

i have used below script :

for now i'm just trying to store qvd for today and day -1 dynamically and append the date with filename

Let vStartDate = Date(Today(),'YYYY/MM/DD');

For d = 0 to 1

Let vDate = Date($(vStartDate)-$(d),'YYYY/MM/DD');

Table:
SQL SELECT * FROM "database"."tablename" WHERE Date = '$(vDate)';

Let vSuffix = 'tablename' &  $(vStartDate);
Store Table into [lib://$(STORE_QVD_CONNECTION)/Table_$(vSuffix).qvd](qvd);

Drop Table Table;

Next d;

[Rodj]

I believe the correct date format for Teradata is "YYYY-MM-DD", give that a go instead of what's in the script currently.

Did you want the whole date appended to the file name? I suggest using the "text" function to convert the date to the format you want, e.g.:

Let vSuffix = 'tablename_' & Text(Date(vStartDate, 'YYYY-MM-DD'))  

[Wayne_101]

i tried, whether - or / it gives me the same result where as vStartDate gives the correct date, just subtracting vStartDare from $(d)  gives year 1900 as shown in attached image

[Rodj · Accepted solution]

Ok, so the problem is the not needed $ expansion, I should have looked at this more closely to star with sorry. Try something like this:

Let vStartDate = Today();
trace 'vStartDate = $(vStartDate)';

For d = 1 to 2
    trace 'd = $(d)';
    Let vDate = Date(vStartDate-d, 'YYYY-MM-DD');
    trace 'vDate = $(vDate)';

    Table:
    SQL SELECT * FROM yourTable
    WHERE 1=1
    AND yourDate = '$(vDate)';

    Let vSuffix = 'tablename' & Date(vStartDate,'YYYYMMDD');
    Trace 'vSuffix is: $(vSuffix)';
    Store Table into 'Path\Table_$(vSuffix).qvd' (qvd);

    Drop Table Table;

Next d;

The trace statements can obviously be removed but will let you see what is happening for debug.

[Wayne_101]

hey ,

Thanks Alot ! it worked.

1 last step can you kindly guide me how to delete qvd from my load script , i wish to maintain a set of 15 days data and would like to delete last date qvd after storing the latest date.

[Rodj]

Glad to hear we're moving forward.

As @treysmithdev  mentioned, to do deletion you need to put your Qlik Sense server into legacy mode (see the help here: https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/LoadData/disable-sta...), not something I'd recommend doing. Personally I like to keep the old files for archive and backup purposes, I've often found having the old ones around to be very handy rather than reloading from Teradata, QVDs are SO much faster. I'd then remove the old files manually sometime down the track when they don't have any value. You could also use something like a scheduled powershell script to clean them, or some other automation tool like Ansible.

If you are happy to run in legacy mode you could use filelist to build a table of the file names in your directory. Then loop through those file names to identify the files you want to remove and use the Execute function to call an OS level delete of the file. Sorry I don't have an environment to test that out in currently!