Qlikview explicit row delimiter? ..I don't want default(enter) row delimiter

[#1]

Hi All,

I have text file with row delimiter is newline(enter). but I want to explicitly specify string(emp*#*) as my delimeter

Sample data is

*****xompanyddata##

*888unwanted****

^unwanted^cfrewavghfohwoaher!

hndvnfa nv;l nlneralsnvg;era

^unwanted^cfrewavghfohwoaher!

emp*#*1234*v bnrkaekbgvbera

dbasfckibaewhbkfbcnkewbr

dept*#*D1

vgbgrqkeijbhfq;kewbvfkwrbvab

bvbkreqkabgvkbrkebkawbgvfkbebrawb

vbnrfkaebjwnvgfkrbvkbvbrebnvner

vnktgnaewvntenglvnlenat

nbgksbntvkn knetknagbvn

nbkntqalnelbnhltnhl

btkrkwbnnbtgn

emp*#*6427

fvgnrflaengvflnr

vnerqaknlvgnlner

vnktnjewagnklneaw

dept*#*D9

vbakrebgvbqkra

nbgktkaewrngnltn

sbgtnfbdrtsbdrtsbdrtsbdrtsgnwlte

gnsrt;kgh

mgnbtlaenwlnglmntl;kmhbth

ghltnrwymhlml5tghymlmh

gnltm4newyghltlmelnhg

mhlrnysnhnlmnrtsh

emp*#*8934

gfjugkaegfkegawdfkgkh123e

fbawkret9340

fgrkiehakhbfg

dept*#*D7

vgnrewlavgnlanrelwangv

vntlwrneglnvltenslng

gvnwteghlntlnlgnvt

nlnlnlnlnlnlnbvgt

mnblrwgnlntlnglnt

from the above sample data I want a row as

Temptable shpould be as :

Row1: *****xompanyddata##*888unwanted****^unwanted^cfrewavghfohwoaher!hndvnfa nv;l nlneralsnvg;era^unwanted^cfrewavghfohwoaher!

Row2:*1234*v nrkaekbgvberadbasfckibaewhbkfbcnkewbrdept*#*D1vgbgrqkeijbhfq;kewbvfkwrbvabbvbkreqkabgvkbrkebkawbgvfkbebrawb

vbnrfkaebjwnvgfkrbvkbvbrebnvnervnktgnaewvntenglvnlenatnbgksbntvkn knetknagbvnnbkntqalnelbnhltnhlbtkrkwbnnbtgn

Row3:....

Further I can extract below table as:

EmpId Department

1234    D1

6427     D9

8934     D7

Attached the sample text file that I'm using.

Thanks

Raj

[#2]

I tried below with explicit delimiter specification but not working, generating only single row.

LOAD @1

FROM

C:\Users\rkotha\Desktop\logfile.txt

(txt, codepage is 1252, no labels, delimiter is 'emp*#*', msq);

[MK_QSL]

Temp:

LOAD Replace(@1,'emp*#*',';;;') as NewField

FROM

logfile.txt

(txt, codepage is 1252, no labels, delimiter is ';', msq);

NoConcatenate

Load SubField(NewField,';;;') as NewField Resident Temp;

Drop Table Temp;

[#4]

I need to capture dept as well as a separate field. The file is just a sample of big file, there could be some EmpID with no deptID as well, need to handle it.

Thanks.

[MK_QSL]

What is the final result you are looking for?

EMPID and DEPTID fields?

[#6]

hi I got nearer to  solution:

tempp:

LOAD concat(@1) AS Data,

SubstringCount(concat(@1),'emp*#*') AS EmplCount,

  SubstringCount(concat(@1),'dept*#*') AS DeptCount

FROM

C:\Users\rkotha\Desktop\logfile.txt

(txt, codepage is 1252, no labels, delimiter is ';', msq);

Let i = Peek('EmplCount');

Let j= Peek('DeptCount');

Let data=Peek('Data');

LOAD

Mid('$(data)',Index('$(data)','emp*#*',IterNo())+6,4) as empID,

Mid('$(data)',Index('$(data)','dept*#*',IterNo())+6,3) as Dept,

IterNo() as Itrcount

AutoGenerate 1 While IterNo()<=$(i);

my output for above code is

Dept	empID
*D1	1234
*D7	6427
*D9	8934

Required solution:

Dept	empID
*D1	1234
*D9	6427
*D7	8934

[#7]

this happen as data getting concatenated in the ascending order instead of load order.

Thanks

Ranjith

[#8]

Got the solution:

1. Need to add sort weight in the concat() function.

below is the changed code.

tempp:

Load

@1 ,

Recno() As Recnumb

FROM

C:\Users\rkotha\Desktop\logfile.txt

(txt, codepage is 1252, no labels, delimiter is ';', msq);

Test:

LOAD concat(@1,';',Recnumb) AS A1,

  SubstringCount(concat(@1,';',Recnumb),'emp*#*') AS EmplCount,

  SubstringCount(concat(@1,';',Recnumb),'dept*#*') AS DeptCount

Resident tempp;

Let i = Peek('EmplCount');

Let j= Peek('DeptCount');

Let data=Peek('A1');

LOAD

Mid('$(data)',Index('$(data)','emp*#*',IterNo())+6,4) as empID,

Mid('$(data)',Index('$(data)','dept*#*',IterNo())+6,2) as Dept,

IterNo() as Itrcount

AutoGenerate 1 While IterNo()<=$(i);