topic Re: help with script in QlikView

help with script

Mon, 03 Aug 2015 11:21:08 GMT

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

Re: help with script

Anonymous — Mon, 03 Aug 2015 11:23:18 GMT

the message is okay, as you create the field month in same script

you Need a preceding load

load *,

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

load

Re: help with script

MayilVahanan — Mon, 03 Aug 2015 11:30:37 GMT

Try like this

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

Load

From datasource'

Re: help with script

sasiparupudi1 — Mon, 03 Aug 2015 11:32:57 GMT

try

(text(makedate(year([snapshot:]),month([snapshot:])) ) & '/' & text('Q' & Ceil((Month([Date Survey Completed])-3)/ 3)) as MonthQuarter

Re: help with script

Mon, 03 Aug 2015 11:34:25 GMT

Hi Mayil,

I have put this script in but it now the first ( before text is in red. There is end bracket.

The error message says "error in expression ")" expected

Re: help with script

Mon, 03 Aug 2015 11:37:05 GMT

Hi Rudolf,

I have put this script in but now the first ( before text is in red. There is no end bracket.

The error message says "error in expression ")" expected

Re: help with script

senpradip007 — Mon, 03 Aug 2015 11:39:56 GMT

Try like

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

Load

From datasource'

Re: help with script

Mon, 03 Aug 2015 11:41:53 GMT

Hi Chris,

just remove the bracket and the preceding load should work for you

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

Load....

Joe

Re: help with script

Anonymous — Mon, 03 Aug 2015 11:42:16 GMT

I copied your Expression. when I Count your brackets one bracket is to much.

so use

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

(first bracket deleted)

Re: help with script

MayilVahanan — Mon, 03 Aug 2015 11:50:17 GMT

Sorry, I didn't notice that

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

Load

From datasource;

Re: help with script

Mon, 03 Aug 2015 12:03:54 GMT

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.

Re: help with script

Anonymous — Mon, 03 Aug 2015 12:10:19 GMT

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?

Re: help with script

MayilVahanan — Mon, 03 Aug 2015 12:11:10 GMT

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

need some gap between as and MonthQuarter.

Re: help with script

Mon, 03 Aug 2015 12:11:30 GMT

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

Re: help with script

rubenmarin — Mon, 03 Aug 2015 12:11:45 GMT

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

Re: help with script

senpradip007 — Mon, 03 Aug 2015 12:13:53 GMT

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

Re: help with script

sunilkumarqv — Mon, 03 Aug 2015 12:17:40 GMT

Hi Chris ,

Just copy this in your script

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

Re: help with script

puttemans — Mon, 03 Aug 2015 12:26:07 GMT

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

Re: help with script

sasiparupudi1 — Mon, 03 Aug 2015 12:39:48 GMT

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

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

Re: help with script

Tue, 04 Aug 2015 08:15:12 GMT

Hi,

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