topic Re: Script in QlikView

Script

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

Hi,

I pulled below SQL table into qlikView:

StartDate	EndDate	QvdName	RefreshRequired
5/5/2016 0:00	5/10/2016 0:00	NewTrades.qvd	Y

How can I write the script to load data from this qvd like

LOAD *
FROM
[..\QVDs\QvdName.qvd](qvd) where Date<=EndDate and Date>=StartDate;

and execute this load statement only when RefreshRequired is 'Y'.

Re: Script

Anil_Babu_Samineni — Thu, 13 Oct 2016 07:48:37 GMT

Perhaps This

LOAD * FROM

[..\QVDs\QvdName.qvd](qvd) where Date<=EndDate and Date>=StartDate and RefreshRequired = 'Y';

Here, What do you want to achieve the DAte

LOAD * FROM

[..\QVDs\QvdName.qvd](qvd) where RefreshRequired = 'Y';

Re: Script

its_anandrjs — Thu, 13 Oct 2016 07:57:46 GMT

Hi,

What is RefreshRequired is this a variable or the field

1. If it is variable then

If RefreshRequired = 'Y' Then

LOAD *

FROM

[..\QVDs\QvdName.qvd](qvd) where Date<=EndDate and Date>=StartDate;

End If

2. If it is field

LOAD *

FROM

[..\QVDs\QvdName.qvd](qvd)

where

Date <= EndDate and Date >= StartDate

and RefreshRequired = 'Y';

Regards

Anand

Re: Script

Anil_Babu_Samineni — Thu, 13 Oct 2016 08:04:07 GMT

Anand, She don't have Date Field, How she Give the restrict for StartDate and EndDate using Date Field. Share me thiughts if you have time?

OR This also Help you

LOAD *,

Interval(Floor(EndDate - StartDate)) as Date

FROM

[..\QVDs\QvdName.qvd](qvd)

where

Date <= EndDate and Date >= StartDate

and RefreshRequired = 'Y';

Re: Script

sunny_talwar — Thu, 13 Oct 2016 09:41:55 GMT

Does the data from SQL will always pull one row? Would you be able to share a sample with expected output?

Re: Script

pooja_sn — Thu, 13 Oct 2016 10:14:44 GMT

Yes.This table will return one row always.

Re: Script

pooja_sn — Thu, 13 Oct 2016 10:17:53 GMT

I wish to load the data from QVD which lies within the date limits only when RefreshRequired flag is 'Y'

Re: Script

ionutbos — Thu, 13 Oct 2016 13:34:07 GMT

Something like this?

let vStart = peek('StartDate',0,'T1');

let vEnd = peek('EndDate',0,'T1');

let vRefresh = peek('RefreshRequired',0,'T1');

let vQVD = peek('QvdName',0,'T1');

//where T1 is the name of the table which contains those fields

If vRefresh = 'Y' Then

LOAD *

FROM

[..\QVDs\$(vQVD)](qvd) where $(vStart)<= Date and Date<=$(vEnd);

End If

Re: Script

Anonymous — Thu, 13 Oct 2016 15:31:24 GMT

This might solve your problem.

DataReload:

load * inline [

StartDate, EndDate, QvdName, RefreshRequired

5/5/2016 0:00, 5/10/2016 0:00, NewTrades.qvd, Y

];

vRefreshrequired= peek('RefreshRequired',0,'DataReload');

if '$(vRefreshrequired)'='Y' then

vQVDname= peek('QvdName',0,DataReload);

vstartdate= peek('StartDate',0,DataReload);

venddate= peek('EndDate',0,DataReload);

load *

FROM

[..\QVDs\$(vQVDname)](qvd)

where Date<=$(venddate) and Date>=$(vstartdate);

end if;

Re: Script

its_anandrjs — Sat, 15 Oct 2016 14:22:18 GMT

Where is the Date field here is it available into the database, Please explain bit more clear with an example or the script.

Regards,

Anand

Re: Script

pooja_sn — Wed, 19 Oct 2016 13:09:06 GMT

I have a QVD stored - NewDesks.qvd which has a column 'Date'.

I have another table in SQL database which has only 1 row with 4 columns - StartDate, EndDate, QVDName, IsRefreshRequired.

I need to load only those records from QVD having Date between StartDate and EndDate which are specified in above SQL table. And the load statement should execute only if IsRefreshRequired column from database has 'Y' value.

Re: Script

Anonymous — Wed, 19 Oct 2016 13:25:15 GMT

Just to understand this better...

Does the reload of the qvw have to be kicked off automatically when there is a refresh required for new records?

Or do you reload the qvw every min/hour/day etc. and during these scheduled reloads, it needs to check for the required reload records?

Re: Script

pooja_sn — Thu, 20 Oct 2016 07:38:05 GMT

The reload of qvw is scheduled to be daily. And the qvd data should be reloaded only if the IsRefreshRequired is true.

Re: Script

Anonymous — Thu, 20 Oct 2016 08:11:18 GMT

Try this:

Edit the path and add in your date filters on the qvd load...

Control_table:
LOAD * INLINE [
StartDate, EndDate, QvdName, RefreshRequired
5/5/2016, 5/10/2016, NewTrades.qvd, Y
];

IF match(trim(peek( 'RefreshRequired', 0, 'Control_table' )),'Y') then

Mytable:
LOAD *
FROM
Path...\NewTrades.qvd
(qvd);

ELSE

TRACE Refresh not required;

ENDIF