topic Re: Calculation in script in QlikView

Calculation in script

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I’m having two different qvd’s and here I need to calculate the front end chart logic in the script itself.

Please let me know how to get this.Below were the QVD’s and logic

Daily_Sign:
LOAD
A7AACD as Location,
A7AIDT as Date,
SIGN_COMPLETE_NEW as Contracts,

'Sign_Inititated' as flag
FROM

(qvd);

Concatenate
Daily_Total:
LOAD

A7AACD as Location,
A7AIDT as Date,
TOTAL_ORDERS_DAILY_NEW as Contracts,
‘Daily_Total' as flag

FROM

(qvd);

Logic: sum({<Date={$(varMaxDate)},flag={'eSign_Inititated'}>}

Contracts)/sum({<Date={$(varMaxDate)},flag={'Daily_Total'}>} Contracts)

Finally I need the above calculation in script itself.

Thanks..

Re: Calculation in script

Gysbert_Wassenaar — Sat, 03 Dec 2016 14:41:38 GMT

Append this to your script:

maxdate:

LOAD max(Date) as maxdate RESIDENT Daily_Sign;

LET varMaxDate = peek('maxdate');

DROP TABLE maxdate;

sum1:

LOAD sum(Contracts) as sum1 RESIDENT Daily_Sign WHERE flag = 'eSign_Initiated' AND DATE = $(varMaxDate);

LET vSum1 = peek('sum1');

DROP TABLE sum1;

sum2:

LOAD sum(Contracts) as sum2 RESIDENT Daily_Sign WHERE flag = 'Daily_Total' AND DATE = $(varMaxDate);

LET vSum2 = peek('sum2');

DROP TABLE sum2;

LET vResult = $(vSum1)/$(vSum2)

Re: Calculation in script

its_anandrjs — Sat, 03 Dec 2016 15:20:27 GMT

Also You can try this way also

maxdate:

LOAD max(Date) as maxdate RESIDENT Daily_Sign;

LET varMaxDate = peek('maxdate');

DROP TABLE maxdate;

sum1:

LOAD sum(Contracts) as sum1 RESIDENT Daily_Sign WHERE flag = 'eSign_Initiated' AND DATE = $(varMaxDate);

concatenate(sum1)

LOAD sum(Contracts) as sum2 RESIDENT Daily_Sign WHERE flag = 'Daily_Total' AND DATE = $(varMaxDate);

FinalResult:

Load

sum1,sum2, sum1/sum2 as Result

resident sum1;

LET vFinalResult = peek('Result',0,'FinalResult');

DROP TABLE FinalResult;

Regards,

Anand

Re: Calculation in script

nareshthavidishetty — Mon, 05 Dec 2016 14:08:38 GMT

Hi,

The calculation is correct.But i need to capture the result by using an flag.

Thanks..

Re: Calculation in script

nareshthavidishetty — Mon, 05 Dec 2016 15:22:12 GMT

Hi,

Below is the script we are using.Here i need to get the calculation as flag.

Temp:
LOAD
'eSign' as flag,
A7AJCD,
A7AACD as Loc,
A7AIDT as Date,
ESIGN_COMPLETE_NEW as Sales,
'Complete'&'-'&ESIGN_COMPLETE_NEW as Category1,
APA0TX
FROM
[..\..\..\QVD\Marketing\esign\Daily_eSign.qvd]
(qvd)Where Len(Trim('Complete'&'-'&ESIGN_COMPLETE_NEW)) > 0;

Concatenate
LOAD
'eSign' as flag,
A7AACD as Loc,

A7AJCD ,
A7AIDT as Date,
'Not Complete'&'-'& ESIGN_NOT_COMPLETE_NEW as Category2,
ESIGN_NOT_COMPLETE_NEW as Sales
FROM
[..\..\..\\QVD\Marketing\esign\Daily_non_eSigndaily.qvd]
(qvd)Where Len(Trim('Not Complete'&'-'& ESIGN_NOT_COMPLETE_NEW)) > 0;

NoConcatenate

load * ,left(Category1,8) as Category Resident Temp;
Concatenate
load * ,left(Category2,12) as Category Resident Temp;
join
Tempp:
Load
min(Date) as minDate,
max(Date) as maxDate
Resident Temp;

Let varMinDate = Num(Peek('minDate', 0, 'Tempp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Tempp'));

drop Table Temp;

Concatenate

LOAD A7AJCD,
'Total Orders' as flag,
A7AACD as Loc,
'' as Category,
A7AIDT as Date,
TOTAL_ORDERS_DAILY_NEW as Sales,
APA0TX
FROM
[..\..\..\QVD\Marketing\esign\Daily_Total.qvd]
(qvd);

A:
Concatenate
LOAD
'Wet Sign' as flag,
A7AACD as Loc,
A7AIDT as Date,
A7AJCD ,
BVA5ST as Category,
Pick(Match(Capitalize(H2P9TX), 'GENERAL ELECTRIC','IL 12MO CSH OPT')+1, Capitalize(H2P9TX), 'Synchrony Bank','COP') as H2P9TX ,
WET_SIGN_DAILY as Sales

FROM
[..\..\..\QVD\Marketing\esign\Daily_eSignwet.qvd]
(qvd) Where Len(Trim(BVA5ST)) > 0;

Logic needs to get in an separate table with flag.

Logic:

Logic: sum({<Date={$(varMaxDate)},flag={'eSign_Inititated'}>}

Contracts)/sum({<Date={$(varMaxDate)},flag={'Daily_Total'}>} Contracts)

Thanks..

Re: Calculation in script

nareshthavidishetty — Mon, 05 Dec 2016 18:15:41 GMT

Hi,

Let me know if it is possible to get flag.

Thanks..

Re: Calculation in script

Gysbert_Wassenaar — Tue, 06 Dec 2016 07:30:36 GMT

What does that mean? What should be calculated where and when (script or charts)?