topic Re: Transform text file in script in QlikView

Transform text file in script

Anonymous — Thu, 14 Dec 2017 14:46:13 GMT

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.

Code	ID	Comment
ABCD	01	This is a good room
ABCD	02	N/A
ABCD	26	Too many chairs
DFTG	01	Not enough dogs
DFTG	07	Too 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;
		ID	01;
		ID	02;
		ID	26;
		ID	07;

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

Re: Transform text file in script

Aurelien_Martinez — Thu, 14 Dec 2017 16:08:04 GMT

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

Re: Transform text file in script

Anonymous — Fri, 15 Dec 2017 09:17:04 GMT

Perfect! Thank you very much.

Re: Transform text file in script

Anonymous — Fri, 15 Dec 2017 14:19:40 GMT

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

grrrrr