topic Import text: row to column (repeat values) in QlikView

Import text: row to column (repeat values)

cdss-developer — Mon, 18 Feb 2019 10:40:58 GMT

Hello I need help with my txt-import script.

I have a text file and it looks like this:

16: DESCRIPTION_1
1 D00234 005
2 D00235 005 Adfds 4.000,00
3 D00236 005
14: DESCRIPTION_2
1 D002234 005
2 D005111 005 Adfds 4.000,00
3 D005112 005
80: DESCRIPTION_3
1 D005222 005
2 D005223 005
3 D005245 005 Adfds 6.000,00
4 D002236 005
-------------------------------------------------------------------------
jdklasdjlkfkls
dasjkdlkfsjdflksd
-------------------------------------------------------------------------
18: DESCRIPTION_25
1 D002234 005
2 D005111 005 Adfds 4.000,00
3 D005112 005
13: DESCRIPTION_6
1 D005222 005
2 D005223 005
3 D005245 005 Adfds 6.000,00
4 D002236 005

I need to have it presented like this:
16: DESCRIPTION_1 1 D00234 005
16: DESCRIPTION_1 2 D00235 005 Adfds 4.000,00
16: DESCRIPTION_1 3 D00236 005
14: DESCRIPTION_2 1 D002234 005
14: DESCRIPTION_2 2 D005111 005 Adfds 4.000,00
14: DESCRIPTION_2 3 D005112 005
80: DESCRIPTION_3 1 D005222 005
80: DESCRIPTION_3 2 D005223 005
80: DESCRIPTION_3 3 D005245 005 Adfds 6.000,00
80: DESCRIPTION_3 4 D002236 005
18: DESCRIPTION_25 1 D002234 005
18: DESCRIPTION_25 2 D005111 005 Adfds 4.000,00
18: DESCRIPTION_25 3 D005112 005
13: DESCRIPTION_6 1 D005222 005
13: DESCRIPTION_6 2 D005223 005
13: DESCRIPTION_6 3 D005245 005 Adfds 6.000,00
13: DESCRIPTION_6 4 D002236 005

So it should eventually look and work like this:
- 7 different columns.
- Whereby the rows that contain ':' are converted (transposed?) and added to the associated row.
- and the dashes (------) and the rows starting with text/ a character should be removed.

Thanks in advance
Greetings,
Eelco

Re: Import text: row to column (repeat values)

ryo_okabe — Mon, 18 Feb 2019 11:20:39 GMT

Hi,

Try this.

------------------------------------------------
Temp:
LOAD @1,
IF(index(@1,':'),@1,peek(A)) as A,

IF(rowno()=1 and not @1 like '*----*',0,
IF(@1 like '*----*' and Peek(B)=0,1,
IF(@1 like '*----*' and Peek(B)>=1,2,
IF(previous(@1) like '*----*' and Peek(B)=2,0,
peek(B))))) as B
FROM
[test.txt]
(txt, codepage is 932, no labels, delimiter is ',', msq);

Mas:
Load A&@1 as Mas
resident Temp
where A<>@1 and B=0 ;

------------------------------------------------

Re: Import text: row to column (repeat values)

cdss-developer — Mon, 18 Feb 2019 13:47:45 GMT

Hi,
Thanks! This has certainly helped me to figure out the right format.
This is what I finally made of it. I think it is still a bit messy, but it is good enough for me to continue.
If anyone has any suggestions for improvement, they are always welcome.
Thanks again!
Greetings,
Eelco

Temp:
LOAD
IF( index(@1,':'),@1&@2,peek(CODE)) as CODE,
IF ( not index (@1,':'),@2) as type2,
@3 as type3,
@4 as type4,
@5 as type5
FROM
.\Data - test.txt
(txt, codepage is 932, no labels, delimiter is ' ', msq);

DEF:
LOad
//CODE as Code1,
subfield(CODE,':',1)&':' as Code1,
subfield(CODE,':',2) as Code2
, type2
, type3
, type4
, type5
Resident Temp
where len (type2)=7;
Drop table Temp;