topic Re: Calculation in load before resident load in QlikView

Calculation in load before resident load

Anonymous — Mon, 25 Jan 2016 13:10:44 GMT

Hi guys,

Is there a way to perform a insertion of data by the user and then do a calculation in script before showing results to user?

Nowadays user alter data by using an inputfield and calculation is done in the front end, in chart expression, which is taking 3 minutes to show results.

Is there a way of doing that?

Re: Calculation in load before resident load

swuehl — Mon, 25 Jan 2016 13:43:57 GMT

If the user uses the desktop client, you can use INPUT function to enter data within the script execution:

Input(str cue [, str caption])

This function can be used in the script only and opens an input box that prompts the user for a value during the script execution. The parameters cue and caption are used as message and caption texts, respectively. The function returns the entered value.

The input box function returns NULL if the dialog is cancelled, closed or cannot be shown.

Example:

Load
Input('Enter value', 'Input box') as v,
Recno () as r
autogenerate 3;

Alternatively, the user could create an Excel file and then th Excel table could be integrated in the script load.

But if your main concern is the performance of your chart expression, I would rather have a look at the expression itself.

Re: Calculation in load before resident load

asgardd2 — Mon, 25 Jan 2016 13:44:52 GMT

Hello! Why your charts are calculated so long? (3 minutes) Can you attach a sample? There is no two-way communication between the script and front end. However, you can use input function in script, or unusual method - macros for your complex calculation.

Re: Calculation in load before resident load

Anonymous — Mon, 25 Jan 2016 13:57:54 GMT

Hi swuehl,

I think using input would not help users, as they have to see chart first and then select which data (in this case is a date) he wants to put further or back from the initial data.

In expression, I am using double if to compare maintenance data>=Workdays and Maintenance date + days of inspection <= Workdays.

This is the real expression I am trying to optimize.
COUNT(distinct if([Date of Maintenance]<=[WORK_DAY],if(lastworkdate([Date of Maintenance]), Round(([Days of Maintenance]/vDays),1))>=[WORK_DAY],[S/N])))* (METER * vConvert)

I tried to use set analysis, but looking at the chart the result is like a constant. It does not show ups and downs from original expression

Re: Calculation in load before resident load

Anonymous — Mon, 25 Jan 2016 14:07:57 GMT

Hi Anton,

Thanks for your response.

Please, see function in my comment above.

Re: Calculation in load before resident load

HirisH_V7 — Mon, 25 Jan 2016 14:18:27 GMT

Hi,

I think users will not go to Script and Enter the fields he requires,

But if you want like that means ,You can add a field Using INLINE One way like this,

Data:
LOAD Customer,
     [Sales Order ID],
     ShipDate as Date,
     Product, 
     Sales,
     Quantity
FROM
[Sales Orders.xls]
(biff, embedded labels, table is [Sales Orders$]);


DataEntry:
LOAD *
INLINE [
Customer, Sales Order ID,Date,  Product, Sales, Quantity
Curtisu, Liu SO691145, 1/9/2016,All-Purpose Bike Stand1 ,1599.00, 5
];

Second way thorugh input box from front end:

INPUTFIELD InputVar;
INPUTFIELD Call;




LOAD Location,
     Value,
     0 as InputVar,
     1 as Call
FROM
Table]
(html, codepage is 1252, embed

ded labels, table is @1);

This will help you to enter the data from front end using INPUT field,

Hope this helps,

Hirish

Re: Calculation in load before resident load

Peter_Cammaert — Mon, 25 Jan 2016 16:07:11 GMT

Check your expression. IMHO it doesn't work at all. There is one parenthesis too many, and at least one relational expression seems broken.

In theory, you should be able to convert this expression into set analysis, which will eliminate the performance penalty of possibly executing millions of double IFs.

Best,

Peter

Re: Calculation in load before resident load

Anonymous — Mon, 25 Jan 2016 17:19:19 GMT

Hi Peter,

I agree. set analysis improves performance in most of cases, but here I am experiencing a different result.

Have a look:

If formula

COUNT(distinct if([Date of Maintenance]>=[WORK_DAY],if(lastworkdate([Date of Maintenance]), Round(([Days of Maintenance]/vDays),1))<=[WORK_DAY],[S/N])))* (METER * vConvert)

Set Analysis

count({$<WORK_DAY={"<=[Date of Maintenance] and >=lastworkdate([Date of Maintenance]), Round(([Days of Maintenance]/vDays),1))"}>}[S/N]) * (METER * vConvert)

So, my desired result is like the first chart, but if I use set analysis I get like second chart.

Re: Calculation in load before resident load

Anonymous — Mon, 25 Jan 2016 17:24:56 GMT

Thanks hirishv7‌!

In my case, I have a preliminary data loaded from script and calculated. After that the results are filtered as users' need and shown in a chart. User can interact with this chart modifying a desired date. This data (a date) needs to be calculated again and updated in chart, That's why I would like to add it in script and do a kind of reload.

Re: Calculation in load before resident load

Peter_Cammaert — Mon, 25 Jan 2016 17:36:37 GMT

This is not proper set analysis either. The specification between braces is either a list of values (a set) or an (advanced) search expression producing a list of values. Sequences of multiple operators like and >= aren't allowed in expressions nor in set analysis. And again, parentheses in your set modifier are unbalanced.

Check your syntax. The first chart looks good, but are you sure it's correct?

Peter

Re: Calculation in load before resident load

Anonymous — Mon, 25 Jan 2016 17:58:54 GMT

Pardon Peter,

Here are the correct formulas.

If formula

COUNT(distinct if([Date of Maintenance]>=[WORK_DAY],if(lastworkdate([Date of Maintenance], Round(([Days of Maintenance]/vDays),1))<=[WORK_DAY],[S/N])))* (METER * vConvert)

Set Analysis

count(distinct {$<WORK_DAY={"<=[Date of Maintenance] and >=lastworkdate([Date of Maintenance], Round(([Days of Maintenance]/vDays),1))"}>}[S/N]) * (METER * vConvert)

The results in first formula is correct, but takes longer as my dataset is big.

In this formula I have only the initial date of maintenance and last date. In chart, client is supposed to see all days from initial to last. That's why i had put <= and >=.

The dataset does not have these dates preload, either because user can change the Date of Maintenance and divide Days of Maintenance per another value.

Do you have any idea of how can I solve it through set analysis?