Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
use a while in your load script together with mid() function to parse in the segments.
See attached sample app.
Regards,
Stefan
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.
BI Consultant
For loop code checked according to Stefan's post suggestions
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
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).
BI Consultant