Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jpsarmet
Contributor
Contributor

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

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?

 

 

7 Replies
Kushal_Chawda

Would you be able to share sample data with example?

jpsarmet
Contributor
Contributor
Author

Sure!

Thanks in advance. 

Kushal_Chawda

What is the output you need for those two fields?

jpsarmet
Contributor
Contributor
Author

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
Creator III
Creator III

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.