Load separate columns in loop

[beaubellamy]

I have a data set that has the date (month and year) as a field with the value of interest in each column for the corresponding date. 

Below is a snapshot of the data file.

What I would like in the available fields is

Type, Route Number, Route Name, Date,  people

Below is my load script. 

I am only able to load the first column values, but I'm still missing the expected date value (yyyy_mm)

set vmonths = 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December', 'January1', 'February1', 'March1', 'April1', 'May1', 'June1';

for each vmonth in $(vmonths)
Trace $(vmonth);

LOAD
"Type",
"Route Number",
"Route Name",

if($(vmonth) = 'January', '2019_01',if($(vmonth) = 'February', '2019_02',if($(vmonth) = 'March', '2019_03',if($(vmonth) = 'April', '2019_04',
if($(vmonth) = 'May', '2019_05',if($(vmonth) = 'June', '2019_06',if($(vmonth) = 'July', '2019_07',if($(vmonth) = 'August', '2019_08',
if($(vmonth) = 'September', '2019_09',if($(vmonth) = 'October', '2019_10',if($(vmonth) = 'November', '2019_11',if($(vmonth) = 'December', '2019_12',
if($(vmonth) = 'January1', '2020_01',if($(vmonth) = 'February1', '2020_02',if($(vmonth) = 'March1', '2020_03',if($(vmonth) = 'April1', '2020_04',
if($(vmonth) = 'May1', '2020_05',if($(vmonth) = 'June1', '2020_06', $(vmonth))))))))))))))))))) as year_month,

$(vmonth) as people

FROM [file];
Next vmonth;

Any help would be appreciated.

[Ksrinivasan · Accepted solution]

Hi,

Why not you try to Cross table function.

It will give your required result.

Regards,

Ksrinivasan

[beaubellamy]

I wasn't aware of this function. Thanks its exactly what I need.