Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

An empty table causes loading to fail

Hi,

I have this serious problem with an empty table. The data comes in csv-file and it is empty. When loading data (in ETL-process) in and concatenating it with existing qvd-file "the loading" fails. The code that I'm using is here:

// -


// Load data from source

// -


Temporary:

LOAD date(@1, 'D-M-YYYY') as transaction_date,

     right(@2, 3) as work_number,

     left(@2, 4) as project_id,

     @3 as line_id,

     @4 as qty,

     @5 as area,

     @6 as company_id,

     'material transactions' as source_system_id

FROM

     $(Data)\dwmtran.csv (txt, codepage is 1252, no labels, delimiter is ';', msq, header is 9 lines);

// -


// Sort the data by date

// -


Fact:

NoConcatenate

LOAD * RESIDENT Temporary ORDER BY transaction_date; // CHANGE DATE

let maxQVDDay = Peek('transaction_date', 0, 'Fact'); // CHANGE THE COLUMN NAME

if $(maxQVDDay) <= "0" then

     let maxQVDDay = DATE((TODAY() + 1));

end if

let companyID = Peek('company_id', 0, 'Fact');

// -


// Join data from QVD

// -


CONCATENATE

LOAD *

FROM

     $(qvdData)\fact_data.qvd (qvd)

WHERE

     // Load history rows for this company only and this source

     (transaction_date < $(maxQVDDay) and company_id = $(companyID) and source_system_id = 'material transactions') // CHANGE THIS

     // Load all rows for all other companies

     OR (company_id <> $(companyID))

     // Load all rows for all other sources

     OR (source_system_id <> 'material transactions');

// -


// Store data back to QVD

// -


STORE Fact INTO $(qvdData)\fact_data.qvd;

DROP TABLE Fact;

DROP TABLE Temporary;

The problem is that when searching the peek date QlikView returns  for maxQVDdate "NULL" value, which although is right (right?). The if-statement seems to be the problem but I don't know how to fix it. I want to loading to proceed even though the csv-file is empty. Do you have any idea how to fix this problem?

-Mikko

1 Solution

Accepted Solutions
MVP
MVP

Re: An empty table causes loading to fail

Hi Mikko,

I was thinking of something like

if isNull($(maxQVDDay))  then

     let maxQVDDay = DATE((TODAY() + 1));

end if

Alternatively, you could check the number of rows read with function NoOfRows('tablename')

LET numRowsFact = NoOfRows('Fact');

if  $(numRowsFact) = 0  then

     let maxQVDDay = DATE((TODAY() + 1));

end if

I think this should work better than filesize.

Regards,

Stefan

5 Replies
MVP
MVP

An empty table causes loading to fail

Hi Mikko,

I think you should test for NULL using isNull() function.

Stefan

Not applicable

An empty table causes loading to fail

Hi swuehl,

and thanks for reply.

I have tried to put OR-statement into if-statement (just trying something ) as like this:

if $(maxQVDDay) <= "0"  OR $(maxQVDDay) = Null() then

     let maxQVDDay = DATE((TODAY() + 1));

end if

And as you can guess, it did not work. How your statement "isNull(exp)" would help (and where to put it)? The csv-file is now empty but in future there will be data.

-Mikko

erich_shiino
Honored Contributor

An empty table causes loading to fail

Hi,

Maybe you can use filesize() to test the size before trying to concatenate it:

filesize( 'xyz.xls' )

Something like:

if (  filesize( 'xyz.xls' ) > ??? (you will have  to try some value that makes sense in your case ) ) then

Temporary:

LOAD date(@1, 'D-M-YYYY') as transaction_date, ..

end if

Hope this helps,

Erich

MVP
MVP

Re: An empty table causes loading to fail

Hi Mikko,

I was thinking of something like

if isNull($(maxQVDDay))  then

     let maxQVDDay = DATE((TODAY() + 1));

end if

Alternatively, you could check the number of rows read with function NoOfRows('tablename')

LET numRowsFact = NoOfRows('Fact');

if  $(numRowsFact) = 0  then

     let maxQVDDay = DATE((TODAY() + 1));

end if

I think this should work better than filesize.

Regards,

Stefan

Not applicable

Re: An empty table causes loading to fail

Hi and thank you for helpful answers,

I tried IsNull(exp) -statement but I faced another problem which comes next when joining the data with existing qvd file.

Checking the number of rows helps, although I have to think the case when the table is not empty; after checking the number of rows and if not 0, then continue with searching the smallest date.

The code would be something like this:

LET numRowsFact = NoOfRows('Fact');

if  $(numRowsFact) = 0  then

     let maxQVDDay = DATE((TODAY() + 1));

ELSE

let maxQVDDay = Peek('transaction_date', 0, 'Fact'); // CHANGE THE COLUMN NAME

if $(maxQVDDay) <= "0" then

     let maxQVDDay = DATE((TODAY() + 1));

end if

Same thing has to be made to company_id otherwise script loading fails again.

-Mikko

Community Browser