Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Mikko,
I think you should test for NULL using isNull() function.
Stefan
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
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
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
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