topic Re: Need to repeat a string function several times in a table and start another one based on the las in App Development

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

jpsarmet — Mon, 15 Jun 2020 19:36:29 GMT

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?

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

Kushal_Chawda — Mon, 15 Jun 2020 21:12:29 GMT

Would you be able to share sample data with example?

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

jpsarmet — Tue, 16 Jun 2020 00:09:25 GMT

Sure!

Thanks in advance.

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

Kushal_Chawda — Tue, 16 Jun 2020 09:03:24 GMT

What is the output you need for those two fields?

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

jpsarmet — Tue, 16 Jun 2020 13:09:01 GMT

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!!

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

Kushal_Chawda — Tue, 16 Jun 2020 13:15:15 GMT

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

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

marcus_sommer — Tue, 16 Jun 2020 13:37:40 GMT

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

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

SerhanKaraer — Wed, 17 Jun 2020 08:10:28 GMT

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.