topic Need help on string parsing in QlikView

Need help on string parsing

Tue, 25 Nov 2014 12:15:06 GMT

Hi,

I have a input string like below (delimited by "|")

"curr_cd|curr_name|currency_symbol|note_num|status_ind|last_modify_dt|modify_person_num|lock_num|AUD|AUD|||1|20141017035818|ADMIN AdminUser|1|CAD|Canada $|||1|20140417090134|ADMIN AdminUser|1|EUR|EUR|||1|20140507172331|Ravi Paul|1|USC|US Cents|||1|20080421173710|ADMIN AdminUser|1|USD|US Dollars|||1|20140911092721|ADMIN AdminUser|52|ZAR|ZAR|||1|20141117081651|ADMIN AdminUser|30|20141125070440|"

Where strings in the bold are fieldnames.

I want to parse this string into a table.

Can anyone give some help.

Final output will be like below,

Re: Need help on string parsing

Gysbert_Wassenaar — Tue, 25 Nov 2014 12:46:42 GMT

In the File Wizard you can choose the Delimiter you need:

The resulting load script should look something like this:

LOAD curr_cd,

curr_name,

currency_symbol,

note_num,

status_ind,

last_modify_dt,

modify_person_num,

lock_num

FROM

[MySourceFile.txt]

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

Re: Need help on string parsing

Tue, 25 Nov 2014 12:57:44 GMT

Hi Gysbert,

My problem is that i am not reading data from any txt file.

I am getting this result as some web service using SOAP.

Re: Need help on string parsing

Gysbert_Wassenaar — Tue, 25 Nov 2014 13:28:36 GMT

In that case you can try using the subfield function.

Re: Need help on string parsing

PradeepReddy — Tue, 25 Nov 2014 14:15:44 GMT

Hi Sachin,

Please find the attached application. It might be helpful.

I have taken an Inline Load, you can change this to the data you are getting from SAOP

Re: Need help on string parsing

Anonymous — Tue, 25 Nov 2014 14:23:25 GMT

What is the last long (red) number in the string doing, a timestamp for the response?

AUD|AUD|||1|20141017035818|ADMIN AdminUser|1|

CAD|Canada $|||1|20140417090134|ADMIN AdminUser|1|

EUR|EUR|||1|20140507172331|Ravi Paul|1|

USC|US Cents|||1|20080421173710|ADMIN AdminUser|1|

USD|US Dollars|||1|20140911092721|ADMIN AdminUser|52|

ZAR|ZAR|||1|20141117081651|ADMIN AdminUser|30|20141125070440|"

Best regards

Nicolai

Re: Need help on string parsing

Anonymous — Tue, 25 Nov 2014 14:36:52 GMT

Beautiful.

I think this does the trick, provided that one concatenated string only is recieved/read at a time.

Re: Need help on string parsing

MarcoWedel — Tue, 25 Nov 2014 23:53:36 GMT

Hi,

one generic solution without hard coded field names could be:

tabTemp:
LOAD FieldNo,
    AutoNumber(RowNo, FieldNo) as RecNo,
    FieldVal;
LOAD IterNo() as RowNo,
    Mod(IterNo()-1,8)+1 as FieldNo,
    SubField(string, '|', IterNo()) as FieldVal
Inline [
string
curr_cd|curr_name|currency_symbol|note_num|status_ind|last_modify_dt|modify_person_num|lock_num|AUD|AUD|||1|20141017035818|ADMIN AdminUser|1|CAD|Canada $|||1|20140417090134|ADMIN AdminUser|1|EUR|EUR|||1|20140507172331|Ravi Paul|1|USC|US Cents|||1|20080421173710|ADMIN AdminUser|1|USD|US Dollars|||1|20140911092721|ADMIN AdminUser|52|ZAR|ZAR|||1|20141117081651|ADMIN AdminUser|30|20141125070440|
]
While IterNo()<=SubStringCount(string,'|');

mapFieldNames:
Mapping LOAD FieldNo, FieldVal
Resident tabTemp
Where RecNo=1;

tabOutput:
Generic LOAD
  RecNo,
  FieldNo,
  FieldVal
Resident tabTemp
Where RecNo>1;

DROP Table tabTemp;

RENAME Fields using mapFieldNames;

hope this helps

regards

Marco

Re: Re: Need help on string parsing

MarcoWedel — Wed, 26 Nov 2014 16:13:53 GMT

even shorter:

tabTemp:
LOAD Div(RowNo()-1,8) as RecNo,
    Mod(RowNo()-1,8) as FieldNo,
    SubField(string, '|') as FieldVal
Inline [
string
curr_cd|curr_name|currency_symbol|note_num|status_ind|last_modify_dt|modify_person_num|lock_num|AUD|AUD|||1|20141017035818|ADMIN AdminUser|1|CAD|Canada $|||1|20140417090134|ADMIN AdminUser|1|EUR|EUR|||1|20140507172331|Ravi Paul|1|USC|US Cents|||1|20080421173710|ADMIN AdminUser|1|USD|US Dollars|||1|20140911092721|ADMIN AdminUser|52|ZAR|ZAR|||1|20141117081651|ADMIN AdminUser|30|20141125070440|
];

mapFieldNames:
Mapping LOAD FieldNo, FieldVal
Resident tabTemp
Where not RecNo;

tabOutput:
Generic LOAD
  RecNo,
  FieldNo,
  FieldVal
Resident tabTemp
Where RecNo;

DROP Table tabTemp;

RENAME Fields using mapFieldNames;

hope this helps

regards

Marco

Re: Need help on string parsing

Thu, 27 Nov 2014 05:30:33 GMT

Thanks all.

Your valuable inputs really helps me.