topic Re: create variable in load script in QlikView

create variable in load script

Anonymous — Mon, 24 Aug 2015 19:30:46 GMT

Hello ,

I have COL1, COL2, COL3 & Total as the columns.

Total is the sum of COL1,COL2, COL3.

I have all the above columns in excel. I wanted to make sure that the Total is caluclated correctly.

I use the load script having COL1, COL2,COL3 and Total but not sure how to create a variable and calculate Vtotal =COl1+COL2+COL3

and then create another variable called VDiff to subtract Total -Vtotal .

I want to see COL1,COL2, COL3, TOTAL , VTOTAL and VDiff in Table Box.

Can you help me

Regards,

Paul

Re: create variable in load script

swuehl — Mon, 24 Aug 2015 19:44:36 GMT

You want to create a calculated field in the LOAD statement, right? (I wouldn't call it variable then, because this has a different meaning in QV scripting and QV frontend)

Maybe something along these lines:

LOAD COL1,

COL2,

COL3,

TOTAL,

Rangesum(COL1, COL2, COL3) as VTOTAL;

FROM Excel.xls;

You can even calculate the difference in the script, like

LOAD *,

TOTAL - VTOTAL as DIFF;

LOAD COL1,

COL2,

COL3,

TOTAL,

Rangesum(COL1, COL2, COL3) as VTOTAL;

FROM Excel.xls;

I use a preceding LOAD here to add a calculated field that itself uses a calculated field in the first, bottom LOAD statement.

Re: create variable in load script

Anonymous — Mon, 24 Aug 2015 19:53:40 GMT

thank you . Tbe more precise, I have the following columns

Exposure

Collaterals

RiskWt_PCT

CalRW

All the above fields are already there in my csv file. I want to usie load script and also calculate the below variable in the load script and show them in Table Box.

What i want to do is first check if the CalRW is calculated correctly.

So i want to create a variable VCalRW and calculate it as VCalRW = Risk_PCT*(Exposure-Collateral)

Now i also want to create another variable VDiff to calculate CalRW - VCalRW.

I want to see Exposure, Collateral,RiskWT_PCT,CalRW, VCalRW and VDiff in the Table box

Re: create variable in load script

swuehl — Mon, 24 Aug 2015 19:58:32 GMT

Should be quite similar to above:

LOAD *,

CalRW- VCalRW as VDiff;

LOAD Exposure,

Collaterals,

RiskWt_PCT,

CalRW,

RiskWt_PCT*(Exposure-Collaterals) as VCalRW;

FROM ...;

Re: create variable in load script

Anonymous — Mon, 24 Aug 2015 20:41:15 GMT

Thank you Swuehi for all the help.

Regards,

Paul