Solved: Loading the data with empty cells - Qlik Community

Report Inappropriate Content · ‎2010-01-30

Hi,

I need to load the data from external QVD files. Some records there contain empty cells. What would be the best way to detect these cells and fill them with some data ("EMPTY" for string and "0000" for number, for example).

I would appreciate an expert advice.

Best regards,

VK.

Anonymous · ‎2010-01-30

Vladimir,

Not sure why you need it to be dual ('EMPTY' and 0), but, assuming you want to convert null values of a field into the required values, you can do it this way:


if(len(trim(field))=0, dual('EMPTY',0), field) as field

The "len(trim(field))=0" is a substitute for "isnull(field)" which should work as well, but may be unreliable. It will also convert the blank data, not only the NULL.

Expect it to slow down the data load. If the speed is important, you can do it in two steps - first load data in a temp table as is, and after that load into the final table using the above method from the temp table.

View solution in original post

Anonymous · ‎2010-01-30

Vladimir,

Not sure why you need it to be dual ('EMPTY' and 0), but, assuming you want to convert null values of a field into the required values, you can do it this way:


if(len(trim(field))=0, dual('EMPTY',0), field) as field

The "len(trim(field))=0" is a substitute for "isnull(field)" which should work as well, but may be unreliable. It will also convert the blank data, not only the NULL.

Expect it to slow down the data load. If the speed is important, you can do it in two steps - first load data in a temp table as is, and after that load into the final table using the above method from the temp table.

Report Inappropriate Content · ‎2010-01-30

Michael,

Thanks for the suggestion. It works perfectly (I am not very concerned about loading time since my system is set to run reload once a day during after-hours).

Regards,

VK