topic Today Date Table Extraction from ODBC Connector in QlikView

Today Date Table Extraction from ODBC Connector

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

Hi Experts,

Can any one please help me on below requirement.

In My Remedy Database daily one Job will run with today date on Job/Table name. By using ODBC Driver extracting the jobs.

The Code is like below. Yesterday I have extracted the A180217AJOB manually by selecting the table from the Data base and stored in to QVD. Today on 180217 there is another job added like A180218P_AJOB. Like this daily one new job will be added with corresponding date and that latest date job has to be stored in the QVD.

Instead of going to the database daily and selecting todays table manually is there any way to load the today's date job here

FROM "USER"."A180217_AJOB"; using for loop in the extraction layer.

The Date which is present in this job contains other dates also along with 180217 date in the current job.

LIB CONNECT TO 'Remedy-ODBC64 (John)';

LOAD

Client,

Date,

START TIME,

END TIME;

[A180209PR1_AJOB]:

SELECT Client,

Date,

START TIME,

END TIME

FROM "USER"."A180217_AJOB";

Thanks in advance.

Re: Today Date Table Extraction from ODBC Connector

mdmukramali — Sun, 18 Feb 2018 05:49:36 GMT

Hi,

Can you try something like this?

LET vToday=Date(Today(),'DDMMYY');

LET vSource = 'A'&$(vToday)&'PR1_AJOB';

LIB CONNECT TO 'Remedy-ODBC64 (John)';

LOAD

Client,

Date,

START TIME,

END TIME;

[A180209PR1_AJOB]:

SELECT Client,

Date,

START TIME,

END TIME

FROM "USER".$(vSource );

// FROM "USER"."$(vSource )";

Re: Today Date Table Extraction from ODBC Connector

kaushiknsolanki — Sun, 18 Feb 2018 05:53:19 GMT

Hi,

Try this, to load only latest date table.

Let vTableName = 'A'&Date(today(),'DDMMYY')&'PR1_AJOB';

LIB CONNECT TO 'Remedy-ODBC64 (John)';

LOAD

Client,

Date,

START TIME,

END TIME;

$(vTableName😞

SELECT Client,

Date,

START TIME,

END TIME

FROM "USER".$(vTableName);

Regards,

Kaushik Solanki

Re: Today Date Table Extraction from ODBC Connector

bhavvibudagam — Sun, 18 Feb 2018 06:49:20 GMT

Hi Koushik,

Thanks for your reply. I need one more small help related to this.

While generating new job on daily after 6 months almost 180 qvds will be generated.

Could you please help me on how to store only last 8 days jobs from today job tables in separate QVDs in the Extraction script.

Suppose When I have extracted the job on today 18/02/18 from data base I have to extract only from today to last 8 days jobs from data base to QVDs i.e.,

Total 9 QVDs need to generate in the folder every time from extraction script. Please help me on this.

1. A180210PR1_AJOB

2. A180211PR1_AJOB

3. A180212PR1_AJOB

4. A180213PR1_AJOB

5. A180214PR1_AJOB

6. A180215PR1_AJOB

7. A180216PR1_AJOB

8. A180217PR1_AJOB

9. A180218PR1_AJOB

Thanks in advance.

Re: Today Date Table Extraction from ODBC Connector

kaushiknsolanki — Sun, 18 Feb 2018 09:19:07 GMT

Try this.

For i=0 to 8

Let vTableName = 'A'&Date(today()-$(i),'DDMMYY')&'PR1_AJOB';

LIB CONNECT TO 'Remedy-ODBC64 (John)';

LOAD

Client,

Date,

START TIME,

END TIME;

Data:

SELECT Client,

Date,

START TIME,

END TIME

FROM "USER".$(vTableName);

Store Data in data.qvd;

Drop table Data;

Regards,

Kaushik Solanki

Re: Today Date Table Extraction from ODBC Connector

bhavvibudagam — Mon, 19 Feb 2018 07:11:25 GMT

Hi Kaushik,

Thanks a lot for your help. Its working