Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
cdss-developer
Contributor III
Contributor III

Import text: row to column (repeat values)

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

1 Solution

Accepted Solutions
ryo_okabe
Partner Ambassador
Partner Ambassador

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 ;

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

View solution in original post

2 Replies
ryo_okabe
Partner Ambassador
Partner Ambassador

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 ;

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

cdss-developer
Contributor III
Contributor III
Author

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;