topic Re: Load Script in QlikView

Load Script

upaliwije — Sun, 22 Dec 2013 06:02:44 GMT

I have following fields in my data table

Note Amount

100 100

101 500

102 300

103 400

200 800

I want to create following variable in my load script

if Note=100 then sum amount as X

if note =101 then sum amount as Y

if Note =200 then sum amount as Z

I have written the load statement like this but it does noe work. Pls help me with correct script

if(NOTE='100',sum(AMOUNT))as X

Re: Load Script

preminqlik — Sun, 22 Dec 2013 06:37:29 GMT

vX=sum({1}Aggr(sum({<Note={100}>}Amount),Note))

Re: Load Script

upaliwije — Sun, 22 Dec 2013 06:50:49 GMT

It is not working in load script

Re: Load Script

upaliwije — Sun, 22 Dec 2013 06:52:40 GMT

Error in expression:

AGGR is not a valid function

TB:

LOAD BAL_ACCT_CODE,

NOTE,

CLA_CODE,

AMOUNT,

vX=sum({1}Aggr(sum({<Note={100}>}Amount),Note)),

BCOD,

Date(MonthEnd(Date#([YEAR] &'-'& [MONTH],'YYYY-MM')),'dd-MMM-yyyy') AS [TRN_DATE]

FROM

[..\QVD\FACT_Table\Trial_Balance.qvd]

(qvd)

WHERE YEAR='2013'

Re: Load Script

preminqlik — Sun, 22 Dec 2013 06:55:34 GMT

you have to write in front end ...go to variable overview

Re: Load Script

upaliwije — Sun, 22 Dec 2013 06:59:51 GMT

sorry

I cant understand what you say. Pls explain

Re: Load Script

preminqlik — Sun, 22 Dec 2013 07:07:27 GMT

you have to define the variable in front end ...press ctrl+alt+v ...and define over there

Re: Load Script

upaliwije — Sun, 22 Dec 2013 07:32:50 GMT

I did as you said and executed the script.

so where can I find the field VX

Re: Load Script

preminqlik — Sun, 22 Dec 2013 07:46:12 GMT

...vx is not a field ...it is a variable..... now u can use that in your expressions by naming $(vX)...send your requirement clearly...

Re: Load Script

upaliwije — Sun, 22 Dec 2013 07:53:35 GMT

TB:

LOAD BAL_ACCT_CODE,

NOTE,

CLA_CODE,

if (CLA_CODE='MC','MOTOR',if(CLA_CODE='M3','MOTOR',if(CLA_CODE='M4','MOTOR','NON_MOTOR')))as MONM,

//YEAR,

//MONTH,

AMOUNT,

BCOD,

Date(MonthEnd(Date#([YEAR] &'-'& [MONTH],'YYYY-MM')),'dd-MMM-yyyy') AS [TRN_DATE]

FROM

[..\QVD\FACT_Table\Trial_Balance.qvd]

(qvd)

WHERE YEAR='2013';

Dear Prem

The above is my data load script. Now I want to create a dimension fiels called 'PREMIUM' (that is sum of amount where note ='100') in the load script. So that dimension I can use for my chart. That is what I want. Pls help

Re: Load Script

preminqlik — Sun, 22 Dec 2013 07:54:28 GMT

if you want to create in load script;

orginal:

Load note,

Amount

from xyz;

Load note as XNote,

sum(Amount) as XAmt

reisdent orginal where note=100 group by note;

Load note as YNote,

sum(Amount) as YAmt

reisdent orginal where note=101 group by note;

let vX=Peek('XAmt',0,'X');

let vY=Peek('YAmt',0,'Y');

Re: Load Script

qlikpahadi07 — Sun, 22 Dec 2013 07:55:42 GMT

I am not sure with the Requirement but it seems you can simple create a Table and with dimension Note and expression as Sum(Amount) you will get desired output.

and same as backend

LOAD BAL_ACCT_CODE,

NOTE,

CLA_CODE,

Sum(AMOUNT) as Amount,

BCOD

FROM

[..\QVD\FACT_Table\Trial_Balance.qvd]

(qvd) Group by BAL_ACCT_CODE,NOTE...all Dimensions

WHERE YEAR='2013'

Else upload a Sample Application and desired output you need

Re: Load Script

preminqlik — Sun, 22 Dec 2013 08:06:14 GMT

cheers same thought .....after finishing the qlikpahadi07 statements .....in chart don't take any dimension , just write the following expression :

sum({<Note={101}>}Amount)