topic Load fields from xlsx files with condition in QlikView

Load fields from xlsx files with condition

Anonymous — Mon, 16 Mar 2015 13:17:27 GMT

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

Re: Load fields from xlsx files with condition

Ralf-Narfeldt — Mon, 16 Mar 2015 13:56:17 GMT

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.

Re: Load fields from xlsx files with condition

Ralf-Narfeldt — Mon, 16 Mar 2015 14:10:50 GMT

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);