Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Use of values from database in the loading script

Hello and happy New Year!
My current loading script uses some hard-coded date values to calculate some extra fields. My client added an extra table in his database to have the said date values be more dynamically modified, without having to modify the whole QlikView loading script. Now my issue is, how can I extract the values from the said table and use them in the loading script?
For the record the hard-coded date values look kind of like this:

LET Cycle1_start = MakeDate(2017,01,10);

Cycle1_stop = MakeDate(2017,04,28);

Cycle2_start = MakeDate(2017,05,02);

Cycle2_stop=MakeDate(2017,09,11);

Cycle3_start=MakeDate(2017,09,12);

Cycle3_stop=MakeDate(2018,01,08);

The "Cycle" table has the following structure:

`id` bigint(20) NOT NULL AUTO_INCREMENT,

  `version` bigint(20) NOT NULL,

  `date_end` datetime NOT NULL,

  `date_start` datetime NOT NULL,

  `deleted` bit(1) NOT NULL,

  `name` varchar(255) NOT NULL,

  PRIMARY KEY (`id`)

ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

1 Solution

Accepted Solutions
luismadriz
Specialist
Specialist

Hi,

You could use the peek function to find the records and fields you need. The below is just an example that assumes the first 3 records have the corresponding cycles...


Cycle: 

Load  *

from cycle

where etc...

order by ...;

Let Cycle1_start = Date(Peek('date_start', 0, 'Cycle'));

Let Cycle1_stop = Date(Peek('date_stop', 0, 'Cycle'));

Let Cycle2_start = Date(Peek('date_start', 1, 'Cycle'));

Let Cycle2_stop = Date(Peek('date_stop', 1, 'Cycle'));

Let Cycle3_start = Date(Peek('date_start', 2, 'Cycle'));

Let Cycle3_stop = Date(Peek('date_stop', 2, 'Cycle'));

Drop Table Cycle;


I hope this helps,


Cheers,


Luis

View solution in original post

2 Replies
YoussefBelloum
Champion
Champion

Hi,

if you already know how to SQL load your database table, just add this after it:

let vDate_end = peek ( 'date_end', 0 , ' the_name_of_your_loaded_table ');


you will have your Date end value stored into the variable and you can use it wherever you want on the script

luismadriz
Specialist
Specialist

Hi,

You could use the peek function to find the records and fields you need. The below is just an example that assumes the first 3 records have the corresponding cycles...


Cycle: 

Load  *

from cycle

where etc...

order by ...;

Let Cycle1_start = Date(Peek('date_start', 0, 'Cycle'));

Let Cycle1_stop = Date(Peek('date_stop', 0, 'Cycle'));

Let Cycle2_start = Date(Peek('date_start', 1, 'Cycle'));

Let Cycle2_stop = Date(Peek('date_stop', 1, 'Cycle'));

Let Cycle3_start = Date(Peek('date_start', 2, 'Cycle'));

Let Cycle3_stop = Date(Peek('date_stop', 2, 'Cycle'));

Drop Table Cycle;


I hope this helps,


Cheers,


Luis