Need to repeat a string function several times in a table and start another one based on the last

[jpsarmet]

Hi all,

I have a table that is composed of just one field and I need to remove the necessary information from the string present in those fields.

Some of these treatments need to be repeated several times.

Example:
LOAD
left(@1,10) as Outlet_cod,
mid(@1,11,3) as Regi_cod,
mid([@1],14,4) as Dist_cod,
mid([@1],18,6) as Terr_cod,
mid([@1],24,1) as Clie_Let,
mid([@1],25,10) as Fcc_cod,
mid([@1],35,15) as Units_PeriodNumber,
mid([@1],395,15) as Values_PeriodNumber,
mid([@1],755,10) as WholeSaler_Code
FROM file.txt;

The mid () in the "Units_PeriodNumber" field must be repeated 24 times. The following "Values_PeriodNumber" also needs to be repeated 24 times, but it starts at the point where the previous mid ends, so in addition to repeating the required amount, it would need to start where the previous one stopped.

Can you guys help me?

[Kushal_Chawda]

Would you be able to share sample data with example?

[jpsarmet]

Sure!

Thanks in advance. 

[Kushal_Chawda]

What is the output you need for those two fields?

[jpsarmet]

I need to create a field for each time I have this string repetition occurrence. 

Example:
LOAD
left (@ 1.10) as Outlet_cod,
mid (@ 1,11,3) as Regi_cod,
mid ([@ 1], 14,4) at Dist_cod,
mid ([@ 1], 18,6) as Terr_cod,
mid ([@ 1], 24,1) as Clie_Let,
mid ([@ 1], 25,10) as Fcc_cod,
mid ([@ 1], 35,15) the Units_PeriodNumber1,
mid ([@ 1], 50,15) the Units_PeriodNumber2,
mid ([@ 1], 65,15) the Units_PeriodNumber3,
... repeat the process until the twenty-fourth time, adding 15 to the past position
mid ([@ 1], 395,15) as Values_PeriodNumber1,
mid ([@ 1], 410,15) as Values_PeriodNumber2,
mid ([@ 1], 425,15) as Values_PeriodNumber3,
... repeat the process until the twenty-fourth time, adding 15 to the past position
mid ([@ 1], 755,10) as WholeSaler_Code
FROM file.txt;

Thanks a lot!!

[Kushal_Chawda]

to be honest this is not the good practice to load the data. Probably your text file should have proper delimited like comma or space using which you can load the data in well structured form

[marcus_sommer]

I think the fastest and most simple way would be just to copy & paste the units- and number-parts each 24 times in the load-statement and then adjusting the mid-parameter and the fieldnames. It may look like an ugly task but I assume it won't take much longer as 5 minutes.

The alternatively to it would be to use loops which run from - to with a step of 15 and creates with several variables a concatenated variable-statement which could be then used within the load. Of course it's possible but to develop here a stable solution especially for the sometimes tricky variable-syntax will cost you much more time.

- Marcus

[SerhanKaraer]

Hello Jpsarmet,

To create a generic solution for splitting fixed width records, you can define an inline table for width of the fields and then by using iterno() and while loop while loading, you can use width-inline table.