topic Re: Script If Expression in QlikView

Script If Expression

Anonymous — Wed, 07 Oct 2015 11:43:39 GMT

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

Re: Script If Expression

jonathandienst — Wed, 07 Oct 2015 11:54:40 GMT

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

Re: Script If Expression

cwolf — Wed, 07 Oct 2015 12:06:14 GMT

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;

Re: Script If Expression

stigchel — Wed, 07 Oct 2015 12:07:07 GMT

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;

Re: Script If Expression

jonathandienst — Wed, 07 Oct 2015 12:07:13 GMT

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.

Re: Script If Expression

Anonymous — Wed, 07 Oct 2015 13:10:01 GMT

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