topic Re: Expression to calculate in QlikView

Expression to calculate

Wed, 02 Apr 2014 13:50:44 GMT

Hello Everybody

I load 2 file

The first one

Load

Total

from file 1 .xls

The second one

Load Quart from 2.xls

How to calculate Ecart = ( quart - Total / 13) knowing that quart and total come from a different file 1.xls and 2.xls

Thank U

Re: Expression to calculate

MK_QSL — Wed, 02 Apr 2014 13:55:02 GMT

There should be a common field between these two tables...

Where is that?

Re: Expression to calculate

Anonymous — Wed, 02 Apr 2014 13:56:22 GMT

simplest way is to have at least one field (unique key) that is common for QV to automatically associate the data so you can do your calculations.

there are other ways using $ sign expansions that you could achieve this but i prefer to keep it simple.

so something like

firsttable:

load Total, UniqueKey from [file 1.xls]

secondtable:

load Quart, UniqueKey from [file 2.xls]

if the unique keys have different names in first table and second table, you can rename one of the columns in your load statement by replacing UniqueKey in one of the tables with "Key as UniqueKey".

Re: Expression to calculate

Wed, 02 Apr 2014 14:01:12 GMT

the common file is Num SS

how can i do it please ??

Re: Expression to calculate

its_anandrjs — Wed, 02 Apr 2014 14:12:01 GMT

If there is common field between them then you are able to join this tables and make single table

TableD:

Load CommanKeyField,

Total from file 1 .xls

Load CommanKeyField,

Quart from 2.xls

Then You can do

Load

CommanField

(( quart - Total ) / 13) as Ecart

Resident TableD;

Drop table TableD;

Re: Expression to calculate

Wed, 02 Apr 2014 14:52:17 GMT

Annand ,

QV indacate me that quart is not found in my table ???

its strange

Re: Expression to calculate

its_anandrjs — Wed, 02 Apr 2014 14:56:48 GMT

Oh sorry use your quart field please find in your table fields

Re: Expression to calculate

Wed, 02 Apr 2014 14:59:09 GMT

Exactly, I have set the right fields

Re: Expression to calculate

its_anandrjs — Wed, 02 Apr 2014 15:01:41 GMT

Provide me your load script please copy paste here.

Re: Re: Expression to calculate

Wed, 02 Apr 2014 15:04:55 GMT

//************************************ Chargement du Fichier de Versemennt PEE ***************************************//

Versement :

LOAD

Matricule as VPEE_Matricule,

[Nouvelle Numerotation] as Pivot_NouvelleNumerotation,

Nom as VPEE_Nom,

Prénom as VPEE_Prénom,

interessement AS VPEE_Interessement,

participation as VPEE_Patricipation,

exceptionnel as VPEE_Exceptionnel,

P01 as VPEE_Montant1,

F01 as VPEE_Fond1,

P02 as VPEE_Montant2,

F02 as VPEE_Fond2,

P03 as VPEE_Montant3,

F03 as VPEE_Fond3,

P04 as VPEE_Montant4,

F04 as VPEE_Fond4,

P05 as VPEE_Montant5,

F05 as VPEE_Fond5,

P06 as VPEE_Montant6,

F06 as VPEE_Fond6,

P07 as VPEE_Montant7,

F07 as VPEE_Fond7,

P08 as VPEE_Montant8,

F08 as VPEE_Fond8,

P09 as VPEE_Montant9,

F09 as VPEE_Fond9,

P10 as VPEE_Montant10,

F10 as VPEE_Fond10,

P11 as VPEE_Montant11,

F11 as VPEE_Fond11,

P12 as VPEE_Montant12,

F12 as VPEE_Fond12,

P01+P02+P03 as VPEE_TotaleGeneral

//+P04+P05+P06+P07+P08+P08+P10+P11+P12

FROM

(biff, embedded labels, table is [export site$]);

//**************************************************** Chargement du salaire **************************** //

Requette :

LOAD

monthname ( '2014-02-1') as date ,

Matr. as VPEE_Matricule,

Montant as Fichier_Salaire_Montant,

[Salaire Base Th] as [Fichier_Salaire_Salaire Base Th],

Quart , // Quart as Fichier_Salaire_Quart,

Commune as lolo,

[Etabl. (SIRET)] as Fichier_Salaire_Etablissement

FROM

(biff, embedded labels, table is Feuil1$);

//Concatenate

//LOAD

// monthname ( '2014-03-1') as date ,

// Matr. as VPEE_Matricule,

// Montant as Fichier_Salaire_Montant,

// [Salaire Base Th] as [Fichier_Salaire_Salaire Base Th],

// Quart as Fichier_Salaire_Quart,

// [Etabl. (SIRET)] as Fichier_Salaire_Etablissement

//FROM

//(biff, embedded labels, table is Feuil1$);

LOAD VPEE_Matricule ,

( Quart - VPEE_TotaleGeneral ) as Ecart

Resident Versement ;

DROP Table Versement

Re: Expression to calculate

Anonymous — Wed, 02 Apr 2014 20:15:31 GMT

you need to join the two tables Versement and Requette. use "join" before Requette.