topic Re: Segment data in QlikView

Segment data

Anonymous — Fri, 26 Aug 2011 10:49:17 GMT

Hi All,

I have a problem

I want to segment my data and i want to know how i can do it.

This is what i have

CleTab CodeSoc CodeTable CodeUsi Data

K05CR 036 32 1 331903331953331703331303321503331103331153331203

K05DB 036 32 1 603190603195603170603130603150603110603115603120

K06CR 036 32 1 331903331953331703331303321503331103331153331203

K06DB 036 32 1 603190603195603170603130603150603110603115603120

For the field data, i want to separe it in 8 parts of 6 character.

See below whant i want to have

CleTab CodeSoc CodeTable CodeUsi Data

K05CR 036 32 1 331903

K05CR 036 32 1 331953

K05CR 036 32 1 331703

K05CR 036 32 1 331303

K05CR 036 32 1 321503

K05CR 036 32 1 331103

...

You have in attached the file

Thanks

Jeff

Segment data

Fri, 26 Aug 2011 11:00:53 GMT

Hi,

Use left() to take few chars from left for data field, furthor use num() on that to encode in numeric.

like num ( left ( data, 5 ) ) as newdata,

HTH

Re: Segment data

swuehl — Fri, 26 Aug 2011 11:03:58 GMT

Hi,

use a while in your load script together with mid() function to parse in the segments.

See attached sample app.

Regards,

Stefan

Re: Segment data

Miguel_Angel_Baeyens — Fri, 26 Aug 2011 11:12:31 GMT

Hi there,

Although Stefan's solution seems the cleanest, here's another attempt to loop the table and get the records. I haven't tested it, but since it took me some time to write it, here it goes

Original:

LOAD CleTab AS Orig_CleTab,

CodeSoc AS Orig_CodeSoc,

CodeTable AS Orig_CodeTable,

CodeUsi AS Orig_CodeUsi,

Data AS Orig_Data

FROM

[Table à faire.xls]

(biff, embedded labels, table is Sheet1$);

FOR i = 1 TO 48 STEP 6

Processed:

LOAD RecNo() AS No,

Orig_CleTab AS CleTab,

Orig_CodeSoc AS Code,

Orig_CodeTable AS CodeTable,

Orig_CodeUsi AS CodeUsi,

Mid(Orig_Data, $(i), 6) AS Data

RESIDENT Original;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

For loop code checked according to Stefan's post suggestions

Segment data

swuehl — Fri, 26 Aug 2011 12:24:11 GMT

Hi Miguel,

do you have experience what is performing better, a while or for-loop?

I would guess it doesn' really matter on a resident table, but maybe when loading from an external data source (because the while doesn't need a repeating query, where the for-loop performs repeating table loads, if not optimized by QV)?

(To be equivalent with my while-approach, I think you could remove the outer for-loop and you should increase the upper limit in the inner loop from 42 to 48, but that's just for the record).

All, have a nice day,

Stefan

Re: Segment data

Miguel_Angel_Baeyens — Fri, 26 Aug 2011 20:57:07 GMT

Hi Stefan,

Actually I haven't tested this case in particular, because both have pros and cons. But on my general experience, the for next loop is usually slower, because is a load with all the transformation (that is very similar to the one in the while loop) but it concatenates in each step, and it adds extra time to the load process that the while does not.

I will try both with several million records in the same computer and get back with the results.

For the record, you're right, and so I correct the code, just in case. Thanks for noting, (that's the way it goes when I don't test before posting).

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica