Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Can you please load a sample file ?
Hi Manish Kachhia,
I added the sample text file in attachment of my first message.
Is this a single field? if not, what is the delimiter?
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.
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 .
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...
Thank you for all your answers
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