topic Re: Loading File with Blank Values and Space delimited in QlikView

Loading File with Blank Values and Space delimited

suryaa30 — Wed, 25 Nov 2020 16:16:04 GMT

Need some help in loading this file format.

I have a file which is space delimited but there are blank values as well.

I am loading the file as Fixedlength
then replacing space with '$'
using mapsubstring replacing multiple '$' with single '$'
using that as delimiter finding the column values.
When i try to load it the the values for KERDAY gets displaced.

Load

CompleteRecord,

RecordNo,

Replace(Replace(CompleteRecord,'KER DAY','KERDAY'),chr(32),'$') as DollarRecord;

//////////////////////////////////////////

LOAD

"@1:n" as CompleteRecord,

RecNo() as RecordNo

FROM [lib://Balancing/Mainframe\Archive\Load test.txt]

(fix, codepage is 28591, embedded labels);

After replacing blanks with Dollar. I need to replce multiple '$' with single dollar. The problem comes when if I have blank values.

If Count of '$' < 10 then replace with 1 '$'

If Count of '$' >10 then replace with 2 '$'

so that the blank value is accommodated.

Map1:

Mapping load *

Inline [

x,y

$$,$

$$$,$

$$$$,$

$$$$$,$

$$$$$$,$

$$$$$$$,$

$$$$$$$$,$

$$$$$$$$$,$

$$$$$$$$$$,$

$$$$$$$$$$$,$

];

Load *,

MapSubString('Map1',DollarRecord) as DollarRecord;

Re: Loading File with Blank Values and Space delimited

sunny_talwar — Mon, 04 Jun 2018 12:07:32 GMT

What exactly are you hoping should happen? I am confused as to what are you looking to get at?

Re: Loading File with Blank Values and Space delimited

olivierrobin — Mon, 04 Jun 2018 12:09:11 GMT

hello

why don't you use a fixed length format ?

you fil seems to contain well aligned columns

that would be easier

using something like that :

LOAD [@1:31],

[@32:45],

[@46:58],

[@59:71],

[@72:80],

[@81:93],

[@94:101],

[@102:n]

FROM

(fix, codepage is 1252, no labels);

Re: Loading File with Blank Values and Space delimited

suryaa30 — Mon, 04 Jun 2018 12:16:06 GMT

I want to replace the '$' with either single or double based on the count.

if count of '$$$$$$$.......' is greater than 10 then replce with '$$'

if count of '$$$$$$$.......' is less than 10 then replace with '$$'

Re: Loading File with Blank Values and Space delimited

suryaa30 — Mon, 04 Jun 2018 12:17:03 GMT

I want to replace the '$' with either single or double based on the count.

if count of '$$$$$$$.......' is greater than 10 then replce with '$$'

if count of '$$$$$$$.......' is less than 10 then replace with '$$'

Re: Loading File with Blank Values and Space delimited

sunny_talwar — Mon, 04 Jun 2018 12:20:43 GMT

Isn't that exactly what your script is doing?

Re: Loading File with Blank Values and Space delimited

suryaa30 — Mon, 04 Jun 2018 12:24:07 GMT

I wanted to see if there is any other standard way instead of using mapsubstring.

Re: Loading File with Blank Values and Space delimited

sunny_talwar — Mon, 04 Jun 2018 12:29:12 GMT

I am not sure if there is a better way to do this... unless the spaces are always the same on each of the row... for example they are always 10 or 20. In that case you can use Replace function....

Re: Loading File with Blank Values and Space delimited

suryaa30 — Mon, 04 Jun 2018 12:31:57 GMT

Thanks. the spaces are not the fixed and the count can vary. I will settle with mapsubstring

Re: Loading File with Blank Values and Space delimited

suryaa30 — Mon, 04 Jun 2018 12:33:43 GMT

hi thanks for the response. This is just a sample file.

Re: Loading File with Blank Values and Space delimited

kruppas78 — Mon, 04 Jun 2018 12:34:32 GMT

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
FROM
[Load test.txt]
(fix, codepage is 1252, embedded labels);

Re: Loading File with Blank Values and Space delimited

marcus_sommer — Mon, 04 Jun 2018 14:22:19 GMT

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;

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

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

t1:
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;

t2:
load *, #Spaces + LenFieldValues as #CharsPerLine;
load *, len(FieldValues) as LenFieldValues;
load
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