Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day!
I'm loading two xlsx files from directory.
First file have one field Date
Second file Have one field YearMonth.
I want to make one field Year, by function Year(Date) from first file and Right(YearMonth,4) from second.
My Script:
SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00р.;-# ##0,00р.';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]';
SET MonthNames='янв;фев;мар;апр;май;июн;июл;авг;сен;окт;ноя;дек';
SET DayNames='Пн;Вт;Ср;Чт;Пт;Сб;Вс';
LET vToDay = today(1)-1;
Directory;
FOR Each File in filelist ('*.xlsx')
Main:
LOAD
if(SubStringCount(FileBaseName(),'Date')=1, Year(Date),
if(SubStringCount(FileBaseName(),'YearMonth')=1, Left(YearMonth,4) )) as Year
FROM
'$(File)'
(ooxml, embedded labels, table is Sheet1);
NEXT File;
But in result i have error:
Field not found <YearMonth>...
Is there any way to solve this problem?
Thanks
You are using the same Load statement for both files, even though they have different fields.
File_Date.xslx does not have YearMonth field
File_YearMonth.xslx does not have Date field.
Best thing would be to load them separately, as you need to manipulate them differently.
Directory;
FileDate:
LOAD *, Year(Date) As Year;
LOAD Date
FROM
File_Date.xlsx
(ooxml, embedded labels, table is Sheet1);
FileYM:
LOAD *, Left(YearMonth,4) As Year;
LOAD YearMonth
FROM
File_YearMonth.xlsx
(ooxml, embedded labels, table is Sheet1);