help with script

[#1]

Hi,

We have the following script built into our app which helps produce a useful chart:

[Date Survey Completed],
      makedate(year([snapshot:]),month([snapshot:])) as Month,
     YearName([snapshot:],0,4) as FinancialYear,
     QuarterName([snapshot:],0,4) as QuarterName,
     'Q' & Ceil((Month([snapshot:])-3)/ 3) as Quarter,

However, we need to add in something that creates a new field called 'month-quarter'. That way we can have a monthly tracker overlayed on a quarter tracker on the same chart. We have tried the folowing expression directly after the lines above; however, it brings up the error saying "field 'month' not found".

(text(Month) & '/' & text('Q' & Ceil((Month([Date Survey Completed])-3)/ 3)) as MonthQuarter,

Can anyone offer a solution throug an expression? - we want to avoid having to manually adding a column on the raw data which whilst works is not ideal.

Chris

[#2]

Hi all,

Thanks for your help. Amended script but still doesn't like expression of:

Load *, text(Month) & '/' & Quarter as MonthQuarter;

It says error in expression:, is not a valid function.

I ahve attached script as it currently is - see word document.

[Anonymous · Accepted solution]

instead of preceding load just use

date(makedate(year([snapshot:]),month([snapshot:])),'MMM') & '/' & 'Q' & Ceil((Month([snapshot:])-3)/ 3) as MonthQuarter

Month might be a reserved word for Qlikview?

[MayilVahanan]

HI

Load *, text(Month) & '/' & Quarter as  MonthQuarter;

need some gap between as and MonthQuarter.

[#5]

you need the space between the 'as' and new field name 'MonthQuarter' as I posted earlier.

[rubenmarin]

Hi Chris, seems there is no blank space between 'Load' and '*', also there is no space between 'as' and 'MonthQuarter'.

[senpradip007]

In your attachment, Load*, text(Month) & '/' & Quarter asMonthQuarter; has been written instead of Load *, text(Month) & '/' & Quarter as MonthQuarter;

[sunilkumarqv]

Hi Chris ,

Just copy this in your script

Load *, text(Month) & '/' & Quarter as MonthQuarter;

[puttemans]

Hi Chris,

Instead of looking at the preceding load, please consider the solutions Sasidhar & Rudolph suggest. They seem the way forward for me. All you do is add this line in your script, and you're done.

Regards,

Johan

[sasiparupudi1]

Hi Chris

You can not refer to expressions as columns in your script. in this case month, which is defined as an expression makedate(year([snapshot:]),month([snapshot:])) .

You have two options

option 1

Use a preceeding load where you could refer to your month column that you have created during the load

or

option2

use the expression directly in your other expressions

(text(YOURExpression) & '/' & text('Q' & Ceil((Month([Date Survey Completed])-3)/ 3)) as MonthQuarter

To me, it is better to use the option2 as it can be done in one load.the option 1 will effect performance if the table is big

HTH

Sasi

[#11]

Hi,

Itseems that the error is due to the lack of space between 'as' and 'MonthQuarter'.Change it and try.