topic Re: Handling NULL values in flat file data source in QlikView

Handling NULL values in flat file data source

Fri, 12 Sep 2014 12:58:21 GMT

Hello,

I am new in QlikView and I visit frequently the Qlik Community to look for help.

This time I didn't find a similar problem to mine, so I will expose it here hope I will find a solution.

I have a text file containing Null values. When opened with Notepad++, I can see the NULL mark.

The texte file is qualified by Field length which causes me a problem because I can't see the NULL marks when opening the file in QlikView.

The attached print screen shows the problem details.

Hope I well explain my problem.

Thanks in advance.

Re: Handling NULL values in flat file data source

MK_QSL — Fri, 12 Sep 2014 13:13:52 GMT

Can you please load a sample file ?

Re: Handling NULL values in flat file data source

Fri, 12 Sep 2014 13:18:58 GMT

Hi Manish Kachhia,

I added the sample text file in attachment of my first message.

Re: Handling NULL values in flat file data source

MK_QSL — Fri, 12 Sep 2014 13:23:00 GMT

Is this a single field? if not, what is the delimiter?

Re: Handling NULL values in flat file data source

Fri, 12 Sep 2014 13:42:39 GMT

I must split the file this way :

Field 1	Field 2	Field 3	Field 4	Field 5
	Azerty	30	XYZ
1111	Test001	15	ABC

Field 1 : 4 charcaters,

Field 2 : 7 characters,

Field 3 : 2 characters,

Field 4 : 3 characters,

Field 5 : 3 characters.

Re: Handling NULL values in flat file data source

JonnyPoole — Fri, 12 Sep 2014 13:42:44 GMT

i don't think Qlik can find/replace these NULLs in the script. I tried loading the file through an ODBC text driver, but it reacts in the same way and i haven't found SQL that can pick up NULL values within a field, but you can use the coalesce() function to replace a field whose entire value is NULL with something else.

So backing up a bit... what exactly do you need to read these NULLS for ? does each NULL represent an empty value for field ? In this case as Manish asked above, i would suggest creating a delimiter in the source file ... could be any character .

Re: Handling NULL values in flat file data source

MK_QSL — Fri, 12 Sep 2014 13:46:51 GMT

We can create the fields you describe using left, Mid and Right function only if the Null Values are either from Starting but any Null between other character can't be identified...

Re: Handling NULL values in flat file data source

Fri, 12 Sep 2014 14:43:13 GMT

Thank you for all your answers

Re: Handling NULL values in flat file data source

marcus_sommer — Fri, 12 Sep 2014 14:47:40 GMT

Maybe you could use a batch-execution before you loaded the file with read + replace + store. Here an example with the first two steps:

sub ReplaceNull

set fso = CreateObject("Scripting.FileSystemObject")

set sourcefile = fso.OpenTextFile("C:\Users\Marcus Sommer\Desktop\TestNull.txt", 1, true)

strSource = sourcefile.readall

msgbox "##" & strSource & "##"

msgbox "##" & replace(strSource, chr(0), ">>>") & "##"

end sub

- Marcus