Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
jonathandienst
Partner - Champion III
Partner - Champion III

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
cwolf
Creator III
Creator III

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
Partner - Master
Partner - Master

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
Partner - Champion III
Partner - Champion III

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
Anonymous
Not applicable
Author

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