topic Re: script issue in QlikView

script issue

Sun, 03 Aug 2014 17:51:32 GMT

hello,

I am facing some issue in scripting can any one correct me.

Syntax error, missing/misplaced FROM:

If(SNO_WEEK=1,'FW' & Right(FISCAL_YEAR_WEEK_NUM,2)),

If(SNO_WEEK >= 1 and SNO_WEEK <= 4,'FW' & Right(Max(FISCAL_YEAR_WEEK_NUM),2) &'-' &'FW' & Right(Min(FISCAL_YEAR_WEEK_NUM),2),

If(SNO_WEEK >= 1 and SNO_WEEK <= 13,'FW' & Right(Max(FISCAL_YEAR_WEEK_NUM),2) &'-' &'FW' & Right(Min(FISCAL_YEAR_WEEK_NUM),2),

If(SNO_WEEK >= 1 and SNO_WEEK <= 52,'FW' & Right(Max(FISCAL_YEAR_WEEK_NUM),2) &'-' &'FW' & Right(Min(FISCAL_YEAR_WEEK_NUM),2),

))))

AS TIMELINE,

Thanks

Re: script issue

Sun, 03 Aug 2014 18:02:47 GMT

I Want output like below based on above script.

TIMELINE

--------------

FW30

FW26-FW30

FW18-FW30

Thanks

Re: script issue

Anonymous — Sun, 03 Aug 2014 18:03:54 GMT

Just looking at the first line :

If ( SNO_WEEK=1,'FW' & Right FISCAL_YEAR_WEEK_NUM,2 ) ) ,

You have have two pair of brackets so that has ended the if statement. Try removing the final bracket. This may well introduce another error and you'll need to sort any subsequent errors one at time.

Re: script issue

aveeeeeee7en — Sun, 03 Aug 2014 18:05:31 GMT

Try this:

If(SNO_WEEK=1,'FW' & Right(FISCAL_YEAR_WEEK_NUM,2),

If(SNO_WEEK >= 1 and SNO_WEEK <= 4,'FW' & Right(Max(FISCAL_YEAR_WEEK_NUM),2) &'-' &'FW' & Right(Min(FISCAL_YEAR_WEEK_NUM),2),

If(SNO_WEEK >= 1 and SNO_WEEK <= 13,'FW' & Right(Max(FISCAL_YEAR_WEEK_NUM),2) &'-' &'FW' & Right(Min(FISCAL_YEAR_WEEK_NUM),2),

If(SNO_WEEK >= 1 and SNO_WEEK <= 52,'FW' & Right(Max(FISCAL_YEAR_WEEK_NUM),2) &'-' &'FW' & Right(Min(FISCAL_YEAR_WEEK_NUM),2),

))))

AS TIMELINE,

Regards

Aviral Nag

Re: script issue

Sun, 03 Aug 2014 18:09:53 GMT

Yes already I have tried that....but this time getting error as Invalid expression.

Thanks

Re: script issue

Anonymous — Sun, 03 Aug 2014 18:16:41 GMT

That error is probably coming from somewhere else in your LOAD statement.

Try commenting out lines from it until it runs without failing and you should be able to find out which is the dodgy line.

Maybe you could post the whole load statement.

Re: script issue

Sun, 03 Aug 2014 18:23:27 GMT

CALENDAR:

LOAD SNO_WEEK,

FISCAL_YEAR_WEEK_NUM,

Right(FISCAL_YEAR_WEEK_NUM,2) as WEEK,

If(SNO_WEEK=1,'FW' & Right(FISCAL_YEAR_WEEK_NUM,2),

If(SNO_WEEK >= 1 and SNO_WEEK <= 4,'FW' & Right(Max(FISCAL_YEAR_WEEK_NUM),2) &'-' &'FW' & Right(Min(FISCAL_YEAR_WEEK_NUM),2),

If(SNO_WEEK >= 1 and SNO_WEEK <= 13,'FW' & Right(Max(FISCAL_YEAR_WEEK_NUM),2) &'-' &'FW' & Right(Min(FISCAL_YEAR_WEEK_NUM),2),

If(SNO_WEEK >= 1 and SNO_WEEK <= 52,'FW' & Right(Max(FISCAL_YEAR_WEEK_NUM),2) &'-' &'FW' & Right(Min(FISCAL_YEAR_WEEK_NUM),2),

))))

AS TIMELINE,

SNO_MONTH,

FISCAL_YEAR_MONTH_NUM

// CREATE_DT,

// CREATE_USER_ID,

// LAST_UPDTD_DT,

// F8

FROM

I have checked it line by line....first if statement is fine....after that I was facing issue

Re: script issue

veidlburkhard — Sun, 03 Aug 2014 18:39:27 GMT

Hi SwathiRaj,

I tried to analyse your if structure and found that when SNO_Week = 1 is true, also SNO_Week >=1 and SNO_Week <= 4 and...and SNO_Week >=1 and SNO_Week <= 52 are true. So there is no real alternative.

Seems you should write:

SNO_Week > 1 and SNO_Week <=4,

SNO_Week >4 and SNO_Week <= 13 etc.

Hope this helps

Burkhard

Re: script issue

Sun, 03 Aug 2014 19:36:34 GMT

Thax for your reply but it wont work here getting same invalid expression

Re: script issue

maxgro — Sun, 03 Aug 2014 19:55:47 GMT

remove min and max

Re: script issue

simenkg — Sun, 03 Aug 2014 22:00:04 GMT

You are using an aggregate function(min and max) without a group by statement.

Add AFTER the FROM statement.:

Group by SNO_WEEK;

Or another field if that is not the right one to group by.

Re: script issue

Mon, 04 Aug 2014 04:56:19 GMT

I have tried all your solutions but still I was facing issue,any more possible solutions.

Thanks

Re: script issue

Mon, 04 Aug 2014 06:49:01 GMT

My first thought will be that I hope you are using GROUP BY clause with this. Since you are calculating Max and Min or fields in the IF condition. Try keeping Hard coded values in IF condition and then check.

And also, if you could calculate Max and Min of fields before in variable or other table. And then use it to join with your calendar, might help you.

Thanks,

Angad

Re: script issue

simenkg — Mon, 04 Aug 2014 07:39:49 GMT

Then you should try to remove the min and max. Here is how you do it:

First create a temporary table.

MinMax:

Load

min(FISCAL_YEAR_WEEK_NUM) as MinWeekNum,

max(FISCAL_YEAR_WEEK_NUM) as MaxWeekNum

from

....

Let vMinWeekNum = peek('MinWeekNum',0,'MinMax');

Let vMaxWeekNum = peek('MaxWeekNum',0,'MinMax');

drop table MinMax;

//Then you load your original table with the if.

If(SNO_WEEK=1,'FW' & Right(FISCAL_YEAR_WEEK_NUM,2),

If(SNO_WEEK >= 1 and SNO_WEEK <= 4,'FW' & Right('$(vMaxWeekNum)',2) &'-' &'FW' & Right('$(vMinWeekNum)',2),

If(SNO_WEEK >= 1 and SNO_WEEK <= 13,'FW' & Right('$(vMaxWeekNum)',2) &'-' &'FW' & Right('$(vMinWeekNum)',2),

If(SNO_WEEK >= 1 and SNO_WEEK <= 52,'FW' & Right('$(vMaxWeekNum)',2) &'-' &'FW' & Right('$(vMinWeekNum)',2),

))))

AS TIMELINE,

Regards

SKG

Re: script issue

Tue, 05 Aug 2014 13:33:02 GMT

Thanku simen looks you are close to my requirement but I need to find min and max numbers based on SNO_WEEK numbers.Your script will give min and max for all data.

see the below link for expected output

http://community.qlik.com/message/582415#582415

Thanks