Loading from txt into one row

[oscarvictory]

Hi all, here is a problem I came across when reading from flat file.

The file type is EDIFACT and after applying the delimeter the fnal struture is like that showing the rows needed:

and this is my result after the load script:

What I would like is all the fields into one row:

Here is my script, any help will be appreciate. Many TKS!

LOAD

if (@1 = 'UNH' and mid( @3,1,6) = 'COPARN', @@3) as FileType,
If (@1 = 'RFF' and mid( @2,1,3) = 'ACA', mid( @2,5,25 )) as ValueField,
If (@1 = 'DTM' and mid( @2,1,2) = '76', mid( @2,4,8 )) as Date,
If (@1 = 'DTM' and mid( @2,1,2) = '76', mid( @2,12,4 )) as Time

FROM [lib://COPARNS (qlik_abc)/*.*]
(txt, codepage is 28591, no labels, delimiter is '+', msq)

// I need only the COPARNS FileType
WHERE match(RecNo( ), 2, 5) and Match( mid( @3,1,6), 'COPARN' )
and Match(  @1, 'RFF' , 'DTM')

[QFabian · Accepted solution]

Hi @oscarvictory , try this please :

Data2:
load * inline [
FileType, ValueField,Date,Time
COPARN,,,
,43678,,
,,20201121,
,,,1630
];

Data3:
Load
*,
rowno() as id
Resident Data2;
drop table Data2;

Data:
Load
FileType
Resident Data3
Where id = 1;

left join
Load
distinct
1 as id,
ValueField
Resident Data3
Where id = 2;
left join
Load
distinct
1 as id,
Date
Resident Data3
Where id = 3;
left join
Load
distinct
1 as id,
Time
Resident Data3
Where id = 4;
drop table Data3;

[oscarvictory]

Hi QFabian,

Many thanks for your quick response. It worked perfect!, but sorry for my ignorance: where do I have to insert my 

LOAD...

FROM [lib://COPARNS (qlik_abc)/*.*]
(txt, codepage is 28591, no labels, delimiter is '+', msq)

RGDS.

[QFabian]

Hi, you should replace this :

Data2:
load * inline [
FileType, ValueField,Date,Time
COPARN,,,
,43678,,
,,20201121,
,,,1630
];

[oscarvictory]

Great!!