Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

No data returned from newly created QVD?

Hi All,


I'm a newbee to QV, hope you can help me.

I have the following problem, I´m trying to create an incremental load and I´m using two helper variables v_MaxIV  and v_MinIV to store invoicenumbers. I have a problem when the QVD is new, that is when I store it and 3 seconds later want to query it.

Chances are my strategy is totally wrong thought..

IF isnull(filetime('DebInvJour.qvd')) then
//file doesnt exists --> FETCH ODBC
CALL FetchXALODBCDebInvJourFromDate
CALL StoreDebInvJour
CALL QueryInvoiceNumbers

END IF

FETCHXALODBCDebInvJourFromDate fetches all records:

SUB FetchXALODBCDebInvJourFromDate
DebInvJour:
QUALIFY *;
ODBC CONNECT TO XAL (XUserId is ZReBCZVMBDZeGXRNZLMB, XPassword is WCEHEZVMBDZeGXRNZbBB);
SQL SELECT DATASET,
TRIM(INVOICEACCOUNT) AS INVOICEACCOUNT,
TRIM(ORDERACCOUNT) AS ORDERACCOUNT,
SALESBALANCE,
TRIM(INVOICENUMBER) AS INVOICENUMBER,
INVOICEDATE
FROM "XAL_SUPERVISOR".DEBINVJOUR WHERE
INVOICEDATE >= TO_DATE('01/10/2013','DD/MM/YYYY') AND DATASET = 'FIK';
END SUB

StoreDebInvJour nicely stores the QVD

SUB StoreDebInvJour
//Als we geen script errors hebben slaan we de data op naar DebInvJour
If ScriptErrorCount = 0 then
STORE DebInvJour INTO [DebInvJour.qvd] (QVD);
SLEEP 1000;
End If
END Sub

But the QueryInvoiceNumbers returns a null for MaxInvNumber and MInInvNumber???

SUB QueryInvoiceNumbers
/Fetch last invoicenumber from QVD
DebInvJour:
load
max(DebInvJour.INVOICENUMBER) as MaxInvNumber,
min(DebInvJour.INVOICENUMBER) as MinInvNumber
FROM
[DebInvJour.qvd]
(
qvd)
WHERE DebInvJour.DATASET = 'FIK';
let v_MaxIV = peek('MaxInvNumber', -1);
let v_MinIV = peek('MinInvNumber', -1);
;
END SUB

If I run the QueryInvoiceNumber method when the QVD already exists it returns min and max as expected??

What am I doing wrong here?

Thanks in advance!


Mike

Tags (3)
1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

Re: No data returned from newly created QVD?

Hi, maybe because you don't disable the Qualify parameter? It still is QUALIFY * at the end of script? You should unqualify it at some point of the script.

//Maybe something like this:

SUB QueryInvoiceNumbers
/Fetch last invoicenumber from QVD

unqualify *;
DebInvJourMinMax:
load
max(DebInvJour.INVOICENUMBER) as MaxInvNumber,
min(DebInvJour.INVOICENUMBER) as MinInvNumber
FROM
[DebInvJour.qvd]
(
qvd)
WHERE DebInvJour.DATASET = 'FIK';
let v_MaxIV = Peek(''MaxInvNumber'',0,'DebInvJourMinMax');
let v_MinIV = Peek(''MinInvNumber'',0,'DebInvJourMinMax');
END SUB

View solution in original post

4 Replies
Highlighted
MVP
MVP

Re: No data returned from newly created QVD?

A quick look says that the following part of your code is wrong.

SQL SELECT DATASET,

TRIM(INVOICEACCOUNT) AS INVOICEACCOUNT,

TRIM(ORDERACCOUNT) AS ORDERACCOUNT,

You can't use QV functions/features under SQL query part.

Highlighted
Not applicable

Re: No data returned from newly created QVD?

Hi Tresesco,

Thanks for your reply

'AS INVOICEACCOUNT' isn't part of the problem. the query works fine.

Regards,

Mike

Highlighted
Contributor III
Contributor III

Re: No data returned from newly created QVD?

Hi, maybe because you don't disable the Qualify parameter? It still is QUALIFY * at the end of script? You should unqualify it at some point of the script.

//Maybe something like this:

SUB QueryInvoiceNumbers
/Fetch last invoicenumber from QVD

unqualify *;
DebInvJourMinMax:
load
max(DebInvJour.INVOICENUMBER) as MaxInvNumber,
min(DebInvJour.INVOICENUMBER) as MinInvNumber
FROM
[DebInvJour.qvd]
(
qvd)
WHERE DebInvJour.DATASET = 'FIK';
let v_MaxIV = Peek(''MaxInvNumber'',0,'DebInvJourMinMax');
let v_MinIV = Peek(''MinInvNumber'',0,'DebInvJourMinMax');
END SUB

View solution in original post

Highlighted
Not applicable

Re: No data returned from newly created QVD?

Mr Janne you're a lifesaver!!

Thanks, works!


Kind regards,

Mike