Segment data

[Anonymous]

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

[#2]

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

[swuehl]

Hi,

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

See attached sample app.

Regards,

Stefan

[Miguel_Angel_Baeyens]

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;
NEXT

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

For loop code checked according to Stefan's post suggestions

[swuehl]

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

[Miguel_Angel_Baeyens]

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