topic Loading the data with empty cells in QlikView

Loading the data with empty cells

Sat, 30 Jan 2010 16:45:14 GMT

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.

Loading the data with empty cells

Anonymous — Sat, 30 Jan 2010 23:03:20 GMT

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.

Loading the data with empty cells

Sun, 31 Jan 2010 01:25:31 GMT

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,