topic An empty table causes loading to fail in QlikView

An empty table causes loading to fail

Mon, 18 Jul 2011 09:02:25 GMT

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

An empty table causes loading to fail

swuehl — Mon, 18 Jul 2011 11:13:28 GMT

Hi Mikko,

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

Stefan

An empty table causes loading to fail

Mon, 18 Jul 2011 11:41:36 GMT

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

An empty table causes loading to fail

erichshiino — Mon, 18 Jul 2011 12:30:20 GMT

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

Re: An empty table causes loading to fail

swuehl — Mon, 18 Jul 2011 12:39:50 GMT

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

Re: An empty table causes loading to fail

Fri, 22 Jul 2011 05:38:26 GMT

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