Qlik Sense Vertical Parsing

[BTIZAG_OA]

Hello experts,

I am trying to parse a TNS file which is txt file listed vertically. I was able to transform it this far, here is the example of data as csv

data:

rowno	string
1	TNSNAME1
2	HOST=DBSERVERNAME1
3	SERVICE_NAME=DBSERVICENAME1
4	TNSNAME2
5	HOST=DBSERVERNAME2
6	SERVICE_NAME=DBSERVICENAME2
7...	.....

Is it possible to transform this like below?

desired output:

TNSNAMES	HOST&SERVICENAME
TNSNAME1	HOST=DBSERVERNAME1 | SERVICE_NAME=DBSERVICENAME1
TNSNAME2	HOST=DBSERVERNAME2 | SERVICE_NAME=DBSERVICENAME2

row 1 is added as first column then row 2&3 is added as second column as concatenated and continues

[TheLazyDevelopr · Accepted solution]

This might work for you:

Test:
Load * INline [
rowno, string
1, TNSNAME1
2, HOST=DBSERVERNAME1
3, SERVICE_NAME=DBSERVICENAME1
4, TNSNAME2
5, HOST=DBSERVERNAME2
6, SERVICE_NAME=DBSERVICENAME2
];

Test2:
Load
If(WildMatch(string,'*TNSNAME*'), string, peek('TNSNAMES')) as TNSNAMES,
If(WildMatch(string, 'HOST=*', 'SERVICE_NAME=*'), string) as HOST_SERVICENAME
Resident Test;

Drop Table Test;

NoConcatenate
Final:
Load
TNSNAMES,
Concat(HOST_SERVICENAME,'|') as HOST_SERVICENAME
Resident Test2
Group by TNSNAMES
;

Drop Table Test2;

Exit script;

[BTIZAG_OA]

Hello Acorona,

thats worked perfectly thank you so much :).

can you please explain how that peek() works in following part i couldnt get that?

If(WildMatch(string,'*TNSNAME*'), string, peek('TNSNAMES')) as TNSNAMES

[TheLazyDevelopr]

used peek in the current field TNSNAMES to get the values inserted into each row because TNSNAMES1 will only show up in the first row since it is true. All the false values will then peek at the first row to insert TNSNAMES1 into each row below. 

https://community.qlik.com/t5/Design/Peek-vs-Previous-When-to-Use-Each/ba-p/1475913