Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load a single field with NO TRIM of spaces around record

Hello !

Have a plain AscII file, variable length records , that I want to load as A SINGLE FIELD (a string with say 80 characters).

Here's a sample of the data :

SBL01 16/04/2010 15:54:14 TC00101
SBL01 16/04/2010 15:54:44 TC00101
SBL01 16/04/2010 15:55:14 TC00101
16/04/2010 07:29:10 TC00101
16/04/2010 07:30:14 TC00101
16/04/2010 07:30:43 TC00101
16/04/2010 07:31:12 TC00101
16/04/2010 07:31:41 TC00101
16/04/2010 07:32:10 TC00101
CDES1 16/04/2010 08:49:49 TC06699 086330 1
CDES1 16/04/2010 08:49:49 TC06701
CDES1 08:49:49 TC00901
CDES1 16/04/2010 08:50:00 TC00202
CDES1 16/04/2010 08:50:01 TC06501
CDES1 16/04/2010 08:50:01 TC06701
CDES1 16/04/2010 08:50:01 TC00901
CDES1 16/04/2010 08:50:04 TC06606
CDES1 16/04/2010 08:50:05 TC06699 086330 1
CDES1 16/04/2010 08:50:05 TC06701
CDES1 16/04/2010 08:50:05 TC00901
CDES1 16/04/2010 08:54:10 TC00101
CDES1 16/04/2010 08:54:38 TC00101
CDES1 16/04/2010 08:54:42 TC06606
CDES1 16/04/2010 08:54:43 TC06699 086330 1
CDES1 16/04/2010 08:54:43 TC06701
CDES1 16/04/2010 08:54:43 TC00901
CBITE 01/04/2010 12:08:10 TC06699 324068 3
CBITE 01/04/2010 12:08:10 TC06701
CBITE 01/04/2010 12:08:10 TC25901
CBITE 01/04/2010 12:08:20 TC26301
CBITE 01/04/2010 12:12:00 TC00101
CBITE 01/04/2010 12:12:18 TC00101
CBITE 01/04/2010 12:12:47
CBITE 01/04/2010 12:13:15 TC00101
CBITE 01/04/2010 12:13:45 TC00101
CBITE 01/04/2010 12:14:14 TC00101
CBITE 01/04/2010 12:14:50 TC00101
CBITE 01/04/2010 12:15:19 TC00101
CBITE 01/04/2010 12:15:48 TC00101
CBITE 01/04/2010 TC00101
CBITE 01/04/2010 12:16:47 TC00101
CBITE 01/04/2010 12:17:16 TC00101
CBITE 01/04/2010 12:17:45 TC00101
CBITE 01/04/2010 12:18:14 TC00101
CBITE 01/04/2010 12:18:43 TC00101
CBITE 01/04/2010 12:19:12 TC00101
CBITE 01/04/2010 12:19:41 TC00101
CBITE 01/04/2010 12:20:10 TC00101
CBITE 01/04/2010 12:20:39 TC00101
CBITE 01/04/2010 12:21:08 TC00101
CBITE 01/04/2010 12:21:37 TC00101

I need to load this data as A SINGLE FIELD, without trimming spaces around the record.

Say record # 4, it should be loaded as " 16/04/2010 07:29:10 TC00101" , that is, with spaces on the left side as on the original record.

How do I do that ?

Thanks !

By the way, have provided a simple kit file for testing purposes...

1 Solution

Accepted Solutions
hector
Specialist
Specialist

Hi, check the variable Verbatim

From the help:

Verbatim

Normally all field values are automatically stripped of preceding and succeeding blank characters (ASCII 32) before being loaded into the QlikView database. Setting this variable to 1 suspends the stripping of blank characters.

Example:

set Verbatim = 1;

Rgds

View solution in original post

11 Replies
Not applicable
Author

Maybe you are looking for something like this:

Test1:
LOAD @1:5 as F1,
@6:25 as F2,
@27:33 as F3,
@34:40 as F4,
@41:42 as F5

FROM
C:\1\TEST189P\TEST189P.TXT
(fix, utf8, record is 1 lines);


See attachment

Not applicable
Author

It is close to what I need.

However, look at this record :

"CBITE 01/04/2010 TC00101"

The time field of this record is not OK.

I really would like simply to load the records as a single string of 80 bytes...

jonathandienst
Partner - Champion III
Partner - Champion III

Adriano

This seems to work (tested on the first 20 of your records:

TEST:
LOAD F1 & F2 & F3 & F4 & F5 AS Full;
LOAD @1:5 AS F1,
@6:16 AS F2,
@17:25 AS F3,
@26:34 AS F4,
@35:n AS F5
FROM
[..\Test Folder\TestData.txt]
(fix, codepage is 1252);

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Is it necessary to explain how to write a field value derivation If condition in case of the value is null?

Anonymous
Not applicable
Author

Cant you just load it with

LOAD @1:80 AS Full
FROM
[..\Test Folder\TestData.txt]
(fix, codepage is 1252);


Not applicable
Author

Nop. It swallows the spaces...

Not applicable
Author

Instead of load a whole string and parse it by left(), mid() and right() functions you can just do this data cleaning by one load statement:

Test1:

Load
F1, F4, F5, F6,
timestamp#(F2 & ' ' & F3, 'DD/MM/YYYY hh:mm:ss') as DateTime
;
load
if(len(F1)=0, 'Default Code', F1) as F1,
if(len(F2)=0, date(today()), F2) as F2,
if(len(F3)=0, '00:00:00', F3) as F3,
if(len(F4)=0, 'Default Code', F4) as F4,
F5,
F6
;

LOAD Trim(@1:5) as F1,
Trim(@6:16) as F2,
Trim(@17:25) as F3,
Trim(@26:33) as F4,
Trim(@34:40) as F5,
Trim(@41:n) as F6
FROM
C:\1\TEST189P\TEST189P.TXT
(fix, utf8);


See attachment.

hector
Specialist
Specialist

Hi, check the variable Verbatim

From the help:

Verbatim

Normally all field values are automatically stripped of preceding and succeeding blank characters (ASCII 32) before being loaded into the QlikView database. Setting this variable to 1 suspends the stripping of blank characters.

Example:

set Verbatim = 1;

Rgds

Not applicable
Author

Yeeeeeeeeeeeeahh !

I had read that before on the docs but I couldnt REMEMBER where it was. I though it was on the LOAD statement documentation but it wasnt !

Perfect ! Simple !

Thank you all guys who have cooperated for the issue. I'm sticking with Verbatim !

Thanks !