How to display the result of calculated variable

[#1]

Hi all,

Here is my problem :

I have to import a CSV file and to create an additional variable in order to show the result of this variable in a QlikView document.

Imported data are labelled "s_GradientBrut"_and "AnticipationCost", I want to create a new variable 'MissingMoney' (s_GradientBrut-s_AnticipationCost) and display it.

Due to the file format of source file I had to add these script line

myTable:

SortieOrganisee_1:

LOAD
Pays_1,
Palier_1,
Variable_1,

//Récupération des variables on utilise le préfixe s_ pour l'onglet Synthèse du CMDB

// Si le palier est de type TAC on place le flag à 1 pour le reconnaître et pouvoir déduire le marketPremium qui ne sert que pour les TACs
if (FindOneOf(Palier_1,'T'),1,0) as s_TACFlag,

// on sépare chaque variable lue dans le champs Palier_1
if (Variable_1='Failure',Données_1) as s_Failure,
if (Variable_1='MarginalCost',Données_1) as s_MarginalCost,
if (Variable_1='MarketPremium',Données_1) as s_MarketPremium,
if (Variable_1='GradientInstalle',Données_1) as s_GradientBrut,
if (Variable_1='AnticipationCost',Données_1) as s_AnticipationCost

Resident SortieBrutReporting_1;
let MissingMoney=sum(s_GradientBrut-s_AnticipationCost);

Until now, I can see the result of "GradientBrut" and "AnticipationCost" but the computed variable is still empty, i used a selectionList with expression and I've selected MissingMoney as an expression (see last screenshot attached)

The datasource is included as an attachement (source.png)

The result displayed is also added as an attachment (paramLB.png).

Thanks for your suggestions and tricks !

[#2]

HELP !!!

[JonnyPoole]

Can you a QVW sample ? I couldn't tie the load script to the data in the screenshot.

[joshabbott]

I think you need to use dollar sign notation on your variable since you're using a 'let':

if($(Variable_1)...

[jonathandienst]

Hi

You cannot do a sum in a let statement like that. You will need another resident load to compute the sums, followed by a Peek to get the value into the variable:

TSum:

LOAD Sum(s_GradientBrut) - Sum(s_AnticipationCost) As MissingMoney

Resident SortieOrganisee_1;

Let MissingMoney = Peek('MissingMoney');

DROP Table TSum;

HTH

Jonathan

[#6]

Thanks Jonathan,

I think the temporary table is the point but unfortunatly the calculated value is incorrect, it seems that the Sum(s_GradientBrut) instruction make the sum of the value of column ADA, BEL, CHE ... (see source file attached), where I would like to have the calculated data only for ADA then for BEL, then for CHE.

[jonathandienst]

Hi

No problem, just group by the field that contains ADA, BEL, ... but then how do you want this vector it to be represented in a variable? QV does not have array variables.

You may need to post a sample of your source data and model (not just pictures) to illustrate your requirements and the problem more clearly.

HTH

Jonathan

[#8]

Ok, Here is the CSV file I use

[#9]

Is it possible to store a list of data with a loop instruction or something instead of using an array variable ?