Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Loading File with Blank Values and Space delimited

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.

1 File.JPG

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.

2 Dollar.JPG

Map1:

Mapping load *

Inline [

x,y

$$,$

$$$,$

$$$$,$

$$$$$,$

$$$$$$,$

$$$$$$$,$

$$$$$$$$,$

$$$$$$$$$,$

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

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

];

Load *,

MapSubString('Map1',DollarRecord) as DollarRecord;

1 Solution

Accepted Solutions
Highlighted

Re: Loading File with Blank Values and Space delimited

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

View solution in original post

11 Replies
Highlighted

Re: Loading File with Blank Values and Space delimited

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

Highlighted
Specialist III
Specialist III

Re: Loading File with Blank Values and Space delimited

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);

Highlighted
Creator II
Creator II

Re: Loading File with Blank Values and Space delimited

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 '$$'

Creator II
Creator II

Re: Loading File with Blank Values and Space delimited

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 '$$'

Highlighted

Re: Loading File with Blank Values and Space delimited

Isn't that exactly what your script is doing?

Capture.PNG

Highlighted
Creator II
Creator II

Re: Loading File with Blank Values and Space delimited

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

Highlighted

Re: Loading File with Blank Values and Space delimited

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

View solution in original post

Highlighted
Creator II
Creator II

Re: Loading File with Blank Values and Space delimited

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

Highlighted
Creator II
Creator II

Re: Loading File with Blank Values and Space delimited

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