topic using chart expressions in scipt editor in QlikView

using chart expressions in scipt editor

Tue, 26 Jul 2011 07:04:05 GMT

Hi,

I have the following table..

Trans:

LOAD [TRANS NO],

[MBR CODE],

[STORE CODE],

[TRANS TYPE],

TRANS_TYPE,

[BILL DATE],

WeekName,

Month,

Year,

Quarter,

[BILL NO],

[BILL SR NO],

[PRODUCT CODE],

QTY,

[RETAIL VALUE],

[GROSS AMT],

DISCOUNT,

[NET AMOUNT],

[MBR TIER],

[CRM_PROMOTION CODE],

[CRM_PROMOTION NAME],

[BASE POINTS - EARN],

[BONUS POINTS - EARN],

[TOTAL EARNED POINTS],

[TOTAL BURNED POINTS],

[TRANS TAG],

[MODE OF PAYMENT],

[SHOPPER_PROMOTION CODE],

[SHOPPER_PROMOTION DESC],

REMARKS,

[PRODUCT DESCRIPTION],

[BRAND NAME],

[PRODUCT FAMILY],

[PRODUCT CATEGORY],

[PRODUCT LINE 1],

[PRODUCT LINE 2],

[PRODUCT LINE 3],

STYLECODE

FROM

E:\trans_old.qvd

(qvd);

I am using the below expression in a straight table/pivot table to get the deisred result..

1. sum( {$<TRANS_TYPE= {'ENROLLMENT'} >} ([NET AMOUNT]) )

2. count(distinct(if(TRANS_TYPE= 'ENROLLMENT' and wildmatch([MBR CODE],'4444*')>0 ,[MBR CODE])))

my question is how do i assign these to a variable in the script editor...and where exactly do i do this?

thanks,

Navneeth

using chart expressions in scipt editor

IAMDV — Tue, 26 Jul 2011 09:06:57 GMT

Hi Navneeth,

This depends on how you wanted to use the variable. Forexample, if you wanted to evaluate the expression within the variable only oncethen you need to declare the variable in Script Editor. However, if you wantedto evaluate the variable on every time the QV engine performs calculation thenyou need to declare the variable in the Variable Overview Window (Alt + Ctrl +V). The variable you declare in script editor can also be viewed in thevariable overview window.

Hope this helps!

Cheers - DV

using chart expressions in scipt editor

Tue, 26 Jul 2011 09:23:28 GMT

Dear DV,

Thanks for your reply..

i already have assigned these expressions to different variables respectively in the Variable Overview Window .. My concern is that post loading these expressions run for more than 3 crore line items and hence i run out of memory or virtual memory...

If it is possible to assign these expressions to a variable in the script, then please help me out in how to declare the same in the above script that i have provided..

thank you,

Navneeth

Re: using chart expressions in scipt editor

Tue, 26 Jul 2011 09:24:35 GMT

Dear DV,

Thanks for your reply..

If it is possible to assign these expressions to a variable in the script, then please help me out in how to declare the same in the above script that i have provided..

thank you,

Navneeth

Re: using chart expressions in scipt editor

kaushiknsolanki — Tue, 26 Jul 2011 09:38:10 GMT

Hi,

The reason of out of memory as per me is the count function.

Try this, hopefully it may solve your problem.

Trans:

LOAD [TRANS NO],

[MBR CODE],

[STORE CODE],

[TRANS TYPE],

TRANS_TYPE,

[BILL DATE],

WeekName,

Month,

Year,

Quarter,

[BILL NO],

[BILL SR NO],

[PRODUCT CODE],

QTY,

[RETAIL VALUE],

[GROSS AMT],

DISCOUNT,

[NET AMOUNT],

[MBR TIER],

[CRM_PROMOTION CODE],

[CRM_PROMOTION NAME],

[BASE POINTS - EARN],

[BONUS POINTS - EARN],

[TOTAL EARNED POINTS],

[TOTAL BURNED POINTS],

[TRANS TAG],

[MODE OF PAYMENT],

[SHOPPER_PROMOTION CODE],

[SHOPPER_PROMOTION DESC],

REMARKS,

[PRODUCT DESCRIPTION],

[BRAND NAME],

[PRODUCT FAMILY],

[PRODUCT CATEGORY],

[PRODUCT LINE 1],

[PRODUCT LINE 2],

[PRODUCT LINE 3],

STYLECODE,

if(TRANS_TYPE= 'ENROLLMENT' and wildmatch([MBR CODE],'4444*'),1) as Counter

FROM

E:\trans_old.qvd

(qvd);

And use the counter field to count the MBR CODE.

To count you should use the sum(Counter).

Regards,

Kaushik Solanki

using chart expressions in scipt editor

IAMDV — Tue, 26 Jul 2011 09:45:10 GMT

Navneeth – There we go... that’s the solution. If need to use link field in count computation, then enter a "1 asLinkfieldcounter" in the relevant table. On front-end, use sum(Linkfieldcounter), you will get the same result as count(counterID) and itis less expensive to do a sum over 1 values.

Hope this helps!

Cheers - DV

Re: using chart expressions in scipt editor

Tue, 26 Jul 2011 10:05:18 GMT

hi Kaushik & DV,

Thanks for your suggestions.. but the counter things will not work for the below reasons...

1. I require the distinct count of members - one member wit code 4444, and with trans_type = Enrollment can be having a lot of line items.

When we are going to sum the counters, it will not give me the distinct count of members, while it will give me the number of times trans_type = Enrollment appears for every respective member code...

please assist.

thanks,

Navneeth