Separate the data into two colums

[SK28]

Hi,

I have a data set as shown in below format in a single column, need help to separate as shown in the output

Main_Data        year           yyyymm           type

2023                  2023                 -                year

2023m1          2023               202301         monthly

2023m2          2023                202302         monthly

2024                 2024                    -                   year

2024m1           2024               202401         Monthly

2023_ps1        2023                   -                     ps1

2023m1_ps1    2023              202301            ps1

2023m12_ps1  2023          202312            ps1

2023_%brd_rsp  2023        -                        %brd_rsp

2023m1_%brd_rsp 2023         202301       %brd_rsp

2023_%_pl_view    2023          -                  %_pl_view

2023m1_%_pl_view  2023       202301   %_pl_view

Main data needs to be broken into year column, yyyymm column and the type column as shown can you please help

[hic]

Try loading the table using the following:

Load
year,
year & Num(month,'00') as yyyymm,
If(Len(type0)>0,type0,
If(Len(month)>0,'monthly','year')) as type
;

Load
Left(Main_Data,4) as year,
SubField(SubField(Main_Data,'_',1),'m',2) as month,
SubField(Main_Data,'_',2) as type0
From File.txt (...);