Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You cannot use an expression in both aggregated form (Max(UNIT.SOLD_MONTH)) and in normal unaggregated form in the same LOAD statement.
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;
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;
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.
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