topic Intra record Loop in QlikView

Intra record Loop

tresB — Sun, 05 Sep 2010 10:23:10 GMT

Hi All,

I have a field say Field1. This field contains string values like:

value1 - [123-2.0-22.3][124-3.0-222.3[.........].......

value2 - [124-10.2-9.00][124-40.1-55.22][123-40.32-365.2]........

Now i need to compute the 123,124...(first three digits after '[')as fields and the following 2.0, 22.3 for 123 and 3.0 , 222.3 for 124 as values too these{123,124} fields. as long as i have understood .. i need a technique of intra-record loop initially.

Can anybody help? I know there are so manu champs who can help !

Please help me out.

Thanks in advance.

Intra record Loop

Oleg_Troyansky — Sun, 05 Sep 2010 17:15:01 GMT

In other words, you need a way of parsing a long string into several substring and then further parsing those substrings into smaller parts, and qualifying some of those parts as Field Names (???) or maybe Key values, and some other parts as key values?

You'll need to use several string functions. Take a close look at the "String Functions" chapter in the book or a Help article. Specifically, I'd look at the following functions:

- subfield()

- index()

- left(), mid(), right()

- TextBetween()

You'll probably need more than one LOAD statement to accomplish what you are planning to accomplish.

Intra record Loop

Mon, 06 Sep 2010 06:10:48 GMT

You can use a feature of the subfield function which returns a variable number of output rows from one input row. Check the doc for details, but here's an example using your data:


SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/MM/YYYY';
SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Directory;
test:
load
 bracket
,code
,subfield(data,'-') as num
;
load
bracket as bracket
,TextBetween(bracket,'[','-') as code
,mid(bracket,index(bracket,'-')+1) as data
where len(bracket) >0
;
LOAD
subfield(@1,']') as bracket
FROM
qvdemo.txt
(txt, codepage is 1252, no labels, msq);
/* qvdemo.txt looks like:
[123-2.0-22.3][124-3.0-222.3]
[124-10.2-9.00][124-40.1-55.22][123-40.32-365.2]
*/

Intra record Loop

tresB — Mon, 06 Sep 2010 07:00:32 GMT

Thanks a lot Steve!

Your idea has helped me a lot.

and thank you Oleg for your reply too.