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: 
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
swuehl
MVP
MVP

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

View solution in original post

5 Replies
swuehl
MVP
MVP

Hi Mikko,

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

Stefan

Not applicable
Author

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

erichshiino
Partner - Master
Partner - Master

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

swuehl
MVP
MVP

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
Author

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