Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
christinehuband
Contributor III
Contributor III

Transform text file in script

I need to some information from a text file and am getting confused about where to go next.

The file is similar to the attached, although this is obviously sample data and the real file has a lot more fields.

The only information I need is the Code, the ID, and the Comments out of this.

   

CodeIDComment
ABCD01This is a good room
ABCD02N/A
ABCD26Too many chairs
DFTG01Not enough dogs
DFTG07Too dark

I've managed to load the file using:

TEMP2:
LOAD If(@1='Code',@1) as Code,
If(@1='Comments',@1) as Comment,
If(@1='ID',@1) as ID,
@2 as Result
FROM
[SiteTest.txt]
(
txt, codepage is 1252, no labels, delimiter is '=', header is 1 lines)
WHERE @1='Code' or @1 = 'Comments' or @1 = 'ID'

but this clearly gives me the information unattached to itself.

   

Code Comment ID Result
Code ABCD;
Code DFTG;
Comments This is a good room;
Comments N/A;
Comments Too many chairs;
Comments Not enough dogs;
Comments Too dark;
ID01;
ID02;
ID26;
ID07;

   

Any steers in the right direction would be most gratefully received.

1 Solution

Accepted Solutions
amartinez35
Partner
Partner

Hi,

test:

LOAD Distinct

  If(IsNull(Code), Peek(Code), Code) as Code,

  Comments,

  If(IsNull(ID), Peek(ID), ID) as ID

;

LOAD

  If(@1='Code', @2) as Code,

  If(@1='Comments', @2) as Comments,

  If(@1='ID', @2) as ID

FROM

C:\Users\AMartinez.STORENEXT\Downloads\SiteTest.txt

(txt, codepage is 1252, no labels, delimiter is '=', msq)

Where Match(@1, 'Code', 'Comments', 'ID');

NoConcatenate

LOAD

  *

Resident test

Where not IsNull(Comments)

;

DROP table test;

Aurélien

View solution in original post

3 Replies
amartinez35
Partner
Partner

Hi,

test:

LOAD Distinct

  If(IsNull(Code), Peek(Code), Code) as Code,

  Comments,

  If(IsNull(ID), Peek(ID), ID) as ID

;

LOAD

  If(@1='Code', @2) as Code,

  If(@1='Comments', @2) as Comments,

  If(@1='ID', @2) as ID

FROM

C:\Users\AMartinez.STORENEXT\Downloads\SiteTest.txt

(txt, codepage is 1252, no labels, delimiter is '=', msq)

Where Match(@1, 'Code', 'Comments', 'ID');

NoConcatenate

LOAD

  *

Resident test

Where not IsNull(Comments)

;

DROP table test;

Aurélien

christinehuband
Contributor III
Contributor III
Author

Perfect! Thank you very much.

christinehuband
Contributor III
Contributor III
Author

Then...the customer tells me that actually, that text file was a database extract...from a database I was already connecting to!

grrrrr