topic Re: Error in the SCript ? in QlikView

Error in the SCript ?

Thu, 30 Oct 2014 12:38:34 GMT

Outstanding:

Load

DR_CR,

TD_DOC_DRCR_FLAG,

TD_DOC_DT,

[Customer No],

TD_DOC_DT_Year,

TD_DOC_DT_Month,

TD_MAIN_ACNT_CODE,

TD_DOC_DTYearMonth,

if(DR_CR='D',

sum(OPENING) +

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT ) ) -

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT ) ),

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT) )-

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT) )-

sum(OPENING)) as SumField

Resident CUR_PRV_TRANS

Group BY [Customer No],TD_DOC_DT_Month,TD_DOC_DT,TD_MAIN_ACNT_CODE;

regards,

Heather

Re: Error in the SCript ?

its_anandrjs — Thu, 30 Oct 2014 12:43:00 GMT

When use Group by you have to use all dimension which is available in the load script

Outstanding:

Load

DR_CR,

TD_DOC_DRCR_FLAG,

TD_DOC_DT,

[Customer No],

TD_DOC_DT_Year,

TD_DOC_DT_Month,

TD_MAIN_ACNT_CODE,

TD_DOC_DTYearMonth,

if(DR_CR='D',

sum(OPENING) +

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT ) ) -

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT ) ),

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT) )-

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT) )-

sum(OPENING)) as SumField

Resident CUR_PRV_TRANS

Group BY

DR_CR,

TD_DOC_DRCR_FLAG,

TD_DOC_DT,

[Customer No],

TD_DOC_DT_Year,

TD_DOC_DT_Month,

TD_MAIN_ACNT_CODE,

TD_DOC_DTYearMonth;

Regards

Anand

Re: Error in the SCript ?

stigchel — Thu, 30 Oct 2014 12:43:14 GMT

All fields without an aggregration should be included in the group by clause

Re: Error in the SCript ?

alexandros17 — Thu, 30 Oct 2014 12:43:32 GMT

try with :

Outstanding:

Load

[Customer No],

TD_DOC_DT_Month,

TD_DOC_DT,

TD_MAIN_ACNT_CODE,

if(DR_CR='D',

sum(OPENING) +

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT ) ) -

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT ) ),

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT) )-

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT) )-

sum(OPENING)) as SumField

Resident CUR_PRV_TRANS

Group BY [Customer No],TD_DOC_DT_Month,TD_DOC_DT,TD_MAIN_ACNT_CODE;

let me know if it works

Re: Error in the SCript ?

PrashantSangle — Thu, 30 Oct 2014 12:45:32 GMT

Hi,

You are missing TD_DOC_DT_Year,TD_DOC_DTYearMonth field from group by.

If you dont want to include it in group by then use

ONLY(TD_DOC_DT_Year) as TD_DOC_DT_Year,

in your script.

When you are using aggreation function like sum() then you have to include all other field in group by clause.

If you want to avoid it to using in group by clause then use ONLY() as i have suggested.

Your expression can be more optimized if you want.

Regards,

Re: Error in the SCript ?

its_anandrjs — Thu, 30 Oct 2014 12:46:02 GMT

If you want to group by by only four fields that is [Customer No],TD_DOC_DT_Month,TD_DOC_DT,TD_MAIN_ACNT_CODE

then use only this four fields in the load script otherwise you have to use all fields in group by statement

Regards

Anand

Re: Error in the SCript ?

Thu, 30 Oct 2014 12:49:52 GMT

Error in Expression :')' expected ...

Re: Error in the SCript ?

alexandros17 — Thu, 30 Oct 2014 12:52:16 GMT

The logic I want to suggest is that besides

[Customer No],

TD_DOC_DT_Month,

TD_DOC_DT,

TD_MAIN_ACNT_CODE,

use the field computed with sum

because only fields in group by can be extracted from a load instruction

Re: Error in the SCript ?

Thu, 30 Oct 2014 12:59:49 GMT

Error in Expression :')' expected ...

Error in the SCript ?

Re: Error in the SCript ?

its_anandrjs — Thu, 30 Oct 2014 13:05:56 GMT

Hi,

Let me know how many fields are to be loaded from the load script if only four requires then comment the rest of the fields because if you use this fields all to be need for the group by statement see the load script

Outstanding:

Load

//DR_CR,

//TD_DOC_DRCR_FLAG,

TD_DOC_DT,

[Customer No],

//TD_DOC_DT_Year,

TD_DOC_DT_Month,

TD_MAIN_ACNT_CODE,

//TD_DOC_DTYearMonth,

if(DR_CR='D',

sum(OPENING) +

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT ) ) -

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT ) ),

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT) )-

sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT) )-

sum(OPENING)) as SumField

Resident CUR_PRV_TRANS

Group BY [Customer No],TD_DOC_DT_Month,TD_DOC_DT,TD_MAIN_ACNT_CODE;

In bold is the script and line start with // is commented that is not loaded.

Regards

Anand

Re: Error in the SCript ?

Thu, 30 Oct 2014 13:16:02 GMT

But Still Error in Expression:')' Expected

Re: Error in the SCript ?

Marcio_Campestrini — Thu, 30 Oct 2014 13:20:51 GMT

Hi Heather

It seems your problem is with the $(vMAX) variable. What's its content?

Márcio

Re: Error in the SCript ?

Thu, 30 Oct 2014 13:24:43 GMT

LET vMAX = max(TD_DOC_DT);

If Required then Change to Month as Maximum , then it is useful for me...

regards,

Heather

Re: Error in the SCript ?

Marcio_Campestrini — Thu, 30 Oct 2014 13:28:26 GMT

Hi Heather

If you look at your image, you can see no value where $(vMAX) should exist. I think that is your problem.

Márcio

Re: Error in the SCript ?

its_anandrjs — Thu, 30 Oct 2014 13:28:34 GMT

Once Check your load script may be some open and close bracket "( ) " problem.

Regards

Anand

Re: Error in the SCript ?

Thu, 30 Oct 2014 13:30:47 GMT

Hi,

where you are setting the Variable value. can you post the entire script?

Re: Error in the SCript ?

its_anandrjs — Thu, 30 Oct 2014 13:32:17 GMT

Did you put the terminator semi colon also like (;) at the end of script.

Anand

Re: Error in the SCript ?

Thu, 30 Oct 2014 13:37:08 GMT

Every thing is Fine, but it is showing : Error in Expression : ')'

Re: Error in the SCript ?

Thu, 30 Oct 2014 13:38:10 GMT

the error is because the varaible value is blank.

you can post the entire script to check why the variable value is blank.

Re: Error in the SCript ?

Fri, 31 Oct 2014 03:04:02 GMT

LET vMAX = max(date(TD_DOC_DT));

CN_Customer_Code_Temp:

LOAD

TD_TH_SYS_ID,

TD_DOC_DT,

TD_DOC_DRCR_FLAG,

TD_DOC_AMT,

TD_MAIN_ACNT_CODE,

TD_SUB_ACNT_CODE as [Customer No]

Resident PRV_TRANS_DETAIL_Temp

WHERE TD_MAIN_ACNT_CODE='106100'

and TD_SUB_ACNT_CODE <> NULL();

Concatenate

LOAD

TD_TH_SYS_ID,

TD_DOC_DT,

TD_DOC_DRCR_FLAG,

TD_DOC_AMT,

TD_MAIN_ACNT_CODE,

TD_SUB_ACNT_CODE AS [Customer No]

Resident CUR_TRANS_DETAIL_Temp

WHERE TD_MAIN_ACNT_CODE='106100'

and TD_SUB_ACNT_CODE <> NULL();

CUR_PRV_TRANS:

load

TD_TH_SYS_ID,

TD_DOC_DT,

month(TD_DOC_DT) as TD_DOC_DT_Month,

year(TD_DOC_DT) as TD_DOC_DT_Year,

date(Date#(year(TD_DOC_DT)&'-'&month(TD_DOC_DT),'YYYY-MMM'),'MMM-YYYY') as TD_DOC_DTYearMonth,

TD_DOC_DRCR_FLAG,

TD_DOC_AMT,

//if(TD_DOC_DRCR_FLAG='D',TD_DOC_AMT,0) as Deb_Doc_Amt,

//if(TD_DOC_DRCR_FLAG='C',TD_DOC_AMT,0) as Cred_Doc_Amt,

TD_MAIN_ACNT_CODE,

[Customer No]

//if(Previous([Customer No])=[Customer No],

//numsum( TD_DOC_AMT, peek( 'Bsum' ) ),TD_DOC_AMT) as Bsum

Resident CN_Customer_Code_Temp;

Outer Join(CUR_PRV_TRANS)

Outstanding:

LOAD COMP_CODE,

ACNT_YEAR,

CAL_YEAR,

MAIN_ACNT_CODE,

SUB_ACNT_CODE as [Customer No],

SUB_ACNT_CODE as Custcode,

OPENING,

DR_CR

FROM

(biff, embedded labels, table is [Sheet 1$]);

drop tables PRV_TRANS_DETAIL_Temp,CUR_TRANS_DETAIL_Temp,CN_Customer_Code_Temp;

Outstanding:

Load

//DR_CR,

//TD_DOC_DRCR_FLAG,

TD_DOC_DT,

[Customer No],

//TD_DOC_DT_Year,

TD_DOC_DT_Month,

TD_MAIN_ACNT_CODE,

//TD_DOC_DTYearMonth,

if(DR_CR='D',

sum(OPENING) +

sum( If( TD_DOC_DT <= $(vMaxDT) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT ) ) -

sum( If( TD_DOC_DT <= $(vMaxDT) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT ) ),

sum( If( TD_DOC_DT <= $(vMaxDT) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT) )-

sum( If( TD_DOC_DT <= $(vMaxDT) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT) )-

sum(OPENING)) as SumField

Resident CUR_PRV_TRANS

Group BY [Customer No],TD_DOC_DT_Month,TD_MAIN_ACNT_CODE;

DROP Table CUR_PRV_TRANS;