Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Leverage your QlikView investment to modernize BI – see how! Join Group
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Re: Loading File with Blank Values and Space delimited

If your goal is just to load the File, i would do it just like Oliver did.

So you get Null Values it its empty.

LOAD TRIM([@1:31]) AS Name,
NUM([@32:46]) AS Value1,
NUM([@47:59]) AS Value2,
NUM([@60:72]) AS Value3,
NUM([@73:80],'0,0%') AS Value4,
NUM([@81:94]) AS Value5,
NUM([@95:102]) AS Value6
[Load test.txt]
fix, codepage is 1252, embedded labels);

MVP & Luminary
MVP & Luminary

Re: Loading File with Blank Values and Space delimited

I think that you need to count the number of spaces between each value and also the lengths of the values to be able to compare them against eachother to detect if values are missing or not and to develop on this basis a replace-approach (for example spaces to tabs and each missing get an additionally tab-char) or a logic to grab the values with a mid(string, n, n) within a loop-approach.

The following isn't a solution (it does make not much sense with just a few sample records and it seems that there are further inconsistenses within the datastructure because the length of the records are not all the same and I'm not sure that it is from all the values + belonging spaces, too) but it might give you some ideas how you could try to handle the case:

set verbatim = 1;

load repeat(chr(32), recno()) as Search, '<' & num(recno(), '00') & '>' as Return , recno() as RecNo
autogenerate 99;

mapping load Search, Return resident t0 order by RecNo desc;
drop tables t0;

load *, textbetween(Replaced, '>', '<', 1) as Measure;
load *, substringcount(Replaced, '<') as #Fields where len(trim(Origin)) >= 1;
load *, trim(mapsubstring('t0map', Origin)) as Replaced, len(trim(Origin)) as LenReplaced;
load replace([@1:n],'KER DAY','KERDAY') as Origin, len(replace([@1:n],'KER DAY','KERDAY')) as LenOrigin, recno() as RecNo
FROM [Load test.txt] (fix, codepage is 1252);

left join(t1)
load max(#Fields) as MaxFields resident t1;

load *, #Spaces + LenFieldValues as #CharsPerLine;
load *, len(FieldValues) as LenFieldValues;
RecNo, textbetween(Replaced, '>', '<', iterno()) as FieldValues,
textbetween(Replaced, '<', '>', iterno()) as #Spaces, iterno() as Iterno
resident t1 while iterno() <= MaxFields;

left join(t2)
load Iterno, max(#CharsPerLine) as MaxCharsPerLine resident t2 group by Iterno;

- Marcus