Script If Expression

[Anonymous]

Can anyone see anything wrong with this expression?

It's comes up with invalid expression error.

LOAD

if(UNIT.SOLD_MONTH >= MonthStart(addmonths(max(UNIT.SOLD_MONTH),-2)) and UNIT.SOLD_MONTH < Monthstart(addmonths(max(UNIT.SOLD_MONTH),1)), 'Active') as BUYER_BUYING_STATUS,

%_BNO

     FROM

  [$(QVD_Path)\BUYER_unit.qvd]

  (qvd)

      group by    %_BNO;

Thanks

Gareth

[jonathandienst]

You cannot use an expression in both aggregated form (Max(UNIT.SOLD_MONTH)) and in normal unaggregated form in the same LOAD statement.

[cwolf]

You can't use UNIT.SOLD_MONTH and max(UNIT.SOLD_MONTH) in one Load Statement.

You need tow steps for this:

TEMP:

LOAD

UNIT.SOLD_MONTH

%_BNO

FROM [$(QVD_Path)\BUYER_unit.qvd](qvd);

left join (TEMP)

load

%_BNO

max(UNIT.SOLD_MONTH) as MAX.SOLD_MONTH

resident TEMP;

BUYER_unit:

load

if(UNIT.SOLD_MONTH >= MonthStart(addmonths(MAX.SOLD_MONTH,-2)) and UNIT.SOLD_MONTH < Monthstart(addmonths(MAX.SOLD_MONTH,1)), 'Active') as BUYER_BUYING_STATUS,

%_BNO

resident TEMP;

drop field MAX.SOLD_MONTH;

drop table TEMP;

[stigchel]

Load the max first and use an aggregation function for other fields then your group by fields, something like:

LOAD max(UNIT.SOLD_MONTH) as maxdate
	FROM [$(QVD_Path)\BUYER_unit.qvd] (qvd);
	//?? Date may need to be formatted to match the format expected by your SQL SELECT or LOAD statement.
	//?? e.g., date(fieldValue('maxdate', 1),'MMDDYYYY');
	LET vMax = chr(39) & fieldValue('maxdate', 1) &chr(39);

LOAD

Concat(DISTINCT if(UNIT.SOLD_MONTH >= MonthStart(addmonths($(vMax),-2)) and UNIT.SOLD_MONTH < Monthstart(addmonths($(vMax),1)), 'Active')) as BUYER_BUYING_STATUS,

%_BNO

     FROM

  [$(QVD_Path)\BUYER_unit.qvd]

  (qvd)

      group by    %_BNO;

[jonathandienst]

So you need a separate load statement to generate the max(UNIT.SOLD_MONTH) values and a join to join these to the main table. Then you can do the comparison.

[Anonymous]

Thanks for all your responses I am still quite new to QV.

Christian - I've added your expressions to my script however it is still coming up with invalid?

Any ideas?

Thanks