topic Re: RE:Script error in QlikView

RE:Script error

Chanty4u — Wed, 16 Dec 2015 09:17:44 GMT

hi all,

I hve master calender when i am usng innerjoin it shwng error......

y it is shwng error in temp calender?

Re: RE:Script error

tamilarasu — Wed, 16 Dec 2015 09:20:37 GMT

Hi Suresh,

Could you post your script or application.?

Re: RE:Script error

Chanty4u — Wed, 16 Dec 2015 09:21:34 GMT

MinMax:

Load

Max(Date) as MaxDate,

Min(Date) as MinDate

Resident TASKDETAILS;

// Creating Date Variables

Let vMinDate=Num(Peek('MinDate',0,'MinMax'));

Let vMaxDate=Num(Peek('MaxDate',0,'MinMax'));

Let vToday = $(vMaxDate);

Drop Table MinMax;

// TEMPORARY CALANDER

TempCal:

Load

Date($(vMinDate)+ RowNo() -1) as TempDate

AutoGenerate $(vMaxDate) - $(vMinDate) +1;

//*****Master Calender*****

MasterCalender:

Load

TempDate as Date,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as Day,

Week(TempDate) as Week,

WeekEnd(TempDate) as WeekEnd,

'Q' &Ceil(Month(TempDate)/3) as Quarter,

Date(MonthStart(TempDate),'MMM-YYYY') as MonthYear,

InYearToDate(TempDate,$(vToday),0) *-1 as CurYTDFlag,

InYearToDate(TempDate,$(vToday),-1) *-1 as LastYTDFlag

Resident TempCal;

Drop Table TempCal;

Re: RE:Script error

tamilarasu — Wed, 16 Dec 2015 09:25:16 GMT

The above script works for me. Where is your inner join.?

Re: RE:Script error

simospa — Wed, 16 Dec 2015 09:28:02 GMT

Hi,

the script you posted works for me too.

What kind of error are you getting?

Re: RE:Script error

Chanty4u — Wed, 16 Dec 2015 09:28:07 GMT

that is calender

below is script

TASKDETAILS:

LOAD

PROJECT_NUMBER,

TASK_PROJECT_DESC AS PROJECT_DESC,

TASK_CODE AS TSK_TASK_CODE,

TASK_NAME AS TSK_TASK_NAME,

// TARGET_START_DATE,

// TARGET_END_DATE,

// EARLY_START_DATE,

// EARLY_END_DATE,

// ACT_START_DATE,

// ACT_END_DATE,

COMPONENTS as TSK_COMPONENTS,

COMPONENT_DESCRIPTION as TSK_COMPONENT_DESCRIPTION,

COST_CODE,

PHYS_COMPLETE_PCT as TSK_PHYS_COMPLETE_PCT,

TASK_ID as TSK_TASK_ID,

PROJ_ID,

PROD_SUPERVISOR_CODE,

PROD_SUPERVISOR_NAME,

DISCIPLINE_CODE,

DISCIPLINE_ID,

DISCIPLINE_NAME,

FACILITY_CODE,

FACILITY_NAME,

LOCATION_CODE,

MAIN_COMP as TSK_MAIN_COMP,

MAIN_COMP_DESC as TSK_MAIN_COMP_DESC ,

MILESTONE as TSK_MILESTONE,

PROGRESS_AS_OF_CURRENT_MONTH,

PROGRESS_AS_OF_LAST_MONTH,

BASE_START_DATE as Date,

'Bstart' as TransType,

BASE_START_DATE

// BASE_END_DATE

FROM [lib://Project Apps/PPROGRESS_P6TASK_Details_V.qvd]

(qvd);

FIRST TABLE

TASKDETAILS1:

INNER JOIN(TASKDETAILS)

LOAD

PROJECT_NUMBER,

TASK_PROJECT_DESC AS PROJECT_DESC,

TASK_CODE AS TSK_TASK_CODE,

TASK_NAME AS TSK_TASK_NAME,

// TARGET_START_DATE,

// TARGET_END_DATE,

// EARLY_START_DATE,

// EARLY_END_DATE,

// ACT_START_DATE,

// ACT_END_DATE,

COMPONENTS as TSK_COMPONENTS,

COMPONENT_DESCRIPTION as TSK_COMPONENT_DESCRIPTION,

COST_CODE,

PHYS_COMPLETE_PCT as TSK_PHYS_COMPLETE_PCT,

TASK_ID as TSK_TASK_ID,

PROJ_ID,

PROD_SUPERVISOR_CODE,

PROD_SUPERVISOR_NAME,

DISCIPLINE_CODE,

DISCIPLINE_ID,

DISCIPLINE_NAME,

FACILITY_CODE,

FACILITY_NAME,

LOCATION_CODE,

MAIN_COMP as TSK_MAIN_COMP,

MAIN_COMP_DESC as TSK_MAIN_COMP_DESC ,

MILESTONE as TSK_MILESTONE,

PROGRESS_AS_OF_CURRENT_MONTH,

PROGRESS_AS_OF_LAST_MONTH,

// BASE_START_DATE,

BASE_END_DATE as Date,

'BEnd' as TransType,

BASE_END_DATE

FROM [lib://Project Apps/PPROGRESS_P6TASK_Details_V.qvd]

(qvd);

TASKDETAILS1:

INNER JOIN(TASKDETAILS)

LOAD

PROJECT_NUMBER,

TASK_PROJECT_DESC AS PROJECT_DESC,

TASK_CODE AS TSK_TASK_CODE,

TASK_NAME AS TSK_TASK_NAME,

// TARGET_START_DATE,

// TARGET_END_DATE,

// EARLY_START_DATE,

// EARLY_END_DATE,

// ACT_START_DATE,

// ACT_END_DATE,

COMPONENTS as TSK_COMPONENTS,

COMPONENT_DESCRIPTION as TSK_COMPONENT_DESCRIPTION,

COST_CODE,

PHYS_COMPLETE_PCT as TSK_PHYS_COMPLETE_PCT,

TASK_ID as TSK_TASK_ID,

PROJ_ID,

PROD_SUPERVISOR_CODE,

PROD_SUPERVISOR_NAME,

DISCIPLINE_CODE,

DISCIPLINE_ID,

DISCIPLINE_NAME,

FACILITY_CODE,

FACILITY_NAME,

LOCATION_CODE,

MAIN_COMP as TSK_MAIN_COMP,

MAIN_COMP_DESC as TSK_MAIN_COMP_DESC ,

MILESTONE as TSK_MILESTONE,

PROGRESS_AS_OF_CURRENT_MONTH,

PROGRESS_AS_OF_LAST_MONTH,

// BASE_START_DATE,

BASE_END_DATE as Date,

'BEnd' as TransType,

BASE_END_DATE

FROM [lib://Project Apps/PPROGRESS_P6TASK_Details_V.qvd]

(qvd);

Re: RE:Script error

Chanty4u — Wed, 16 Dec 2015 09:30:43 GMT

PFA

error

Re: RE:Script error

tamilarasu — Wed, 16 Dec 2015 09:33:03 GMT

What is your Date field format (BASE_START_DATE).? Seems the Min and max date not taken properly.

Re: RE:Script error

simospa — Wed, 16 Dec 2015 09:34:14 GMT

Hi,

in this line

Date($(vMinDate)+ RowNo() -1) as TempDate

AutoGenerate $(vMaxDate) - $(vMinDate) +1;

it seems that the two variables are empty. You can see that even in TASKDETAILS 0 rows are fetched.

So the issue, in my opinion, is not on calendar part of script but in TASKDETAILS joins.

Re: RE:Script error

Anonymous — Wed, 16 Dec 2015 09:34:44 GMT

Hi suresh,

How can we run this script?specify the error which you are getting in detail.

Re: RE:Script error

Chanty4u — Wed, 16 Dec 2015 09:34:57 GMT

example:

date --- 14-05-15

Re: RE:Script error

tamilarasu — Wed, 16 Dec 2015 09:37:12 GMT

Change the MinMax table script like below,

MinMax:

Load

Max(Date#(Date,'DD-MM-YY')) as MaxDate,

Min(Date#(Date,'DD-MM-YY')) as MinDate

Resident TASKDETAILS;

Re: RE:Script error

jagan — Wed, 16 Dec 2015 09:40:34 GMT

Hi Suresh,

The variables vMaxDate & vMinDate values are empty. Verify below code carefully

MinMax:

Load

Max(Date) as MaxDate,

Min(Date) as MinDate

Resident TASKDETAILS;

// Creating Date Variables

Let vMinDate=Num(Peek('MinDate',0,'MinMax'));

Let vMaxDate=Num(Peek('MaxDate',0,'MinMax'));

Let vToday = $(vMaxDate);

Drop Table MinMax;

// TEMPORARY CALANDER

TempCal:

Load

Date($(vMinDate)+ RowNo() -1) as TempDate

AutoGenerate $(vMaxDate) - $(vMinDate) +1;

Re: RE:Script error

Chanty4u — Wed, 16 Dec 2015 09:45:13 GMT

but values are there....what might be the reason?

Re: RE:Script error

Chanty4u — Wed, 16 Dec 2015 09:55:49 GMT

yeah...its wrkng now... but... ihve five dates its shwng in diffrnt rows...

but i want in same row?

PFA'

in task code i hve 5......in dates its shwng in diff rows

Re: RE:Script error

Chanty4u — Wed, 16 Dec 2015 10:07:46 GMT

error got resolved but ...date values shwng in diff rows

Re: RE:Script error

jagan — Wed, 16 Dec 2015 10:16:22 GMT

Hi,

All this fields are in Same table? Then do Group by in script, or use Max() for all the date fields.

Regards,

jagan.

Re: RE:Script error

Chanty4u — Wed, 16 Dec 2015 10:21:53 GMT

yeah... all in same table..how can i do?

Re: RE:Script error

jagan — Thu, 17 Dec 2015 01:11:19 GMT

Hi,

Try like this

Data:

SELECT

Field1,

Field2,

Sum(Field3) AS Field3,

Sum(Field4) AS Field4

FROM TableName

Group By Field1, Field2;