Handling NULL values in flat file data source

[#1]

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.

[MK_QSL]

Can you please load a sample file ?

[#3]

Hi Manish Kachhia,

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

[MK_QSL]

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

[#5]

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.

[JonnyPoole]

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 . 

[MK_QSL]

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...

[#8]

Thank you for all your answers

[marcus_sommer]

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