Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
Why not you try to Cross table function.
It will give your required result.
Regards,
Ksrinivasan
Hi,
Why not you try to Cross table function.
It will give your required result.
Regards,
Ksrinivasan
I wasn't aware of this function. Thanks its exactly what I need.