Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
garethct
Contributor

Script If Expression

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

5 Replies
MVP
MVP

Re: Script If Expression

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
chrwolf64
Contributor III

Re: Script If Expression

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;

Partner
Partner

Re: Script If Expression

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;

MVP
MVP

Re: Script If Expression

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
garethct
Contributor

Re: Script If Expression

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