Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

input sum of value from another table

hello,

I need some help for one request.

I want to fill the field durée_travail with the sum of the value of the field temps of a another table (origine1) which has several lines with the same id but with different statut.

Table origine 1

N_mantis (id),statut,temps

11,open,2

11,work in progress,4

11, closed,6

17,open,5

17,pending customer,8

17, closed,6

Table origine 2

N_mantis (id),temps

11, 6

17,5

The field temps is the sum of only the statuts open and work in progress of the lines which have the same id (N_mantis) of the Table origine 1

Origine1

Load

      N_mantis

      statut,

       temps

FROM

(
ooxml, embedded labels, table is Evolutions);


origine2

LOAD

      N_mantis ,
     
categorie as Cat,
     
compteur as comp,
    
version as ver,
    
severite as sev,     
    
date_changement as date1 ,
     
Prioritaire as prio,
    
[Etat en cours] as etet,
    
Date_fin as date2,
    
Statut as stat,
    
durée_travail

RESIDENT origine
where N_mantis <> peek('N_mantis');

1 Solution

Accepted Solutions
Not applicable

Re: input sum of value from another table

I've found it ! yes

Statuts:

LOAD *, (Tempsfin + tempsdebut + jourtravail) as durée_travail ;

LOAD recno() as "Code",

    
id,    

    
date_changement as Date_début ,

    
//calcul des heures travaillées sur le jour début

     if ( NetWorkDays(date_changement,date_changement,$(vHolidayList) ) >=1, if ( Hour (date_changement ) + ((Minute (date_changement )/60))<8.5,1,

    
if ( Hour (date_changement ) + ((Minute (date_changement )/60))>= 18.5,0, ((18.5 - (Hour (date_changement ) + ((Minute (date_changement )/60))))/10)) ),0) as tempsdebut,

    
// mise en forme de la date début selon les périodes

     date(monthstart(date_changement), 'MMM-YYYY') AS MoisAnnéeDate_début,

 
date(Daystart(date_changement), 'DD-MMM-YYYY') as AnnéeMoisJourDate_début,

 
dual(week(date_changement)&'-'&WeekYear(date_changement), WeekStart(date_changement)) as SemaineAnnéeDate_début,

 
Year (date_changement) as AnnéeDate_début,   

    
Date_fin,

    
// mise en forme de la date début selon les périodes

     date(monthstart(Date_fin), 'MMM-YYYY') AS MoisAnnéedatefin,

 
date(Daystart(Date_fin), 'DD-MMM-YYYY') as AnnéeMoisJourdatefin,

 
dual(week(Date_fin)&'-'&WeekYear(Date_fin), WeekStart(Date_fin)) as SemaineAnnéedatefin,

 
Year (Date_fin) as AnnéeDate_fin,

    
//calcul des heures travaillées sur le jour fin

      if (NetWorkDays(Date_fin,Date_fin,$(vHolidayList) ) >=1, if ( Hour (Date_fin ) + ((Minute (Date_fin )/60))<8.5,0,

   
if (Hour (Date_fin ) + ((Minute (Date_fin )/60))>= 18.5,1, ((((Hour (Date_fin ) + (Minute (Date_fin )/60))-8.5)) /10))),0) as Tempsfin,

     
//calcul des jours travaillés entre la date début et la date fin

      NetWorkDays(date_changement,Date_fin,$(vHolidayList)) as jourtravail,

    
Statut



FROM



(
ooxml, embedded labels, table is Données);



Mantis:

LOAD

 
distinct id,

 
categorie as Catégorie,

    
severite as sévérité,

    
version,

 
// modification des libellés du champ prioritaire 

     if(Prioritaire='Basse', dual('1-Basse', 1),

   
if(Prioritaire ='Normale', dual('2-Normale', 2),

      
if(Prioritaire ='Elevée', dual('3-Elevée', 3)

         )

      )

   )
as Prioritaire,

    
[Etat en cours]





FROM



(
ooxml, embedded labels, table is Données);





Evolution:

join (Mantis) LOAD id,

1
as Compteur_Evolution,

    
charge,

    
date_livraison,

    
description



FROM



(
ooxml, embedded labels, table is Evolutions);







LEFT JOIN (Mantis) LOAD

id,

sum(durée_travail) as durée_travail2

resident Statuts



where Statut = 'Affecté' or Statut = 'Nouveau'  group by id;

3 Replies
MVP
MVP

Re: input sum of value from another table

I believe you can do this with a group by load of table 1 and a join:

First load your tables then

LEFT JOIN (origine2) LOAD

N_mantis,

sum(temps) as durée_travail

resident origine1

where statut = 'open' or statut = 'work in progress' group by N_mantis;

Not applicable

Re: input sum of value from another table

Thanks swuehl,
But this method generates too many lines...
the first table (origine) is all data about the incidents,with one line per statut for each id (N_mantis)
The second table (mantis) is the synthesis of the incident, so i need only one line per id, with the total of duration time (durée_travail).
With your method, the second table has got now several lines per ID, with one line with all data (this one is good) and the other lines have blank data (theses one i don't need).
ScreenHunter_93 Jul. 08 10.38.gif
I wanted to calculate the sum of three fields with only two statuts

sum

(Tempsfin) + sum(tempsdebut) + sum(jourtravail) as durée_travail
where Statut = 'Affecté' or Statut = 'Nouveau' group by N_mantis;

the sum is correct, but i have only one statut, not the sum of the two statuts...
I also  would like to fill some fields of mantis table with data from origine table :
- statut_courant of mantis table is the value of Staut field of origine table which has the highest date_changement for the same N_mantis
- Date_début_mantis of mantis table is the value of date_changement field of origine table which has the value "Nouveau" in the field  Statut of origine table for the same N_mantis
- Date_fin_mantis of mantis table is the value of date_changement field of origine table which has the value "Fermé" in the field  Statut of origine table for the same N_mantis
this is the actaul script :
SET
ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';





//Let vStartTime = num(maketime(8,30)); //8:30

//Let vCloseTime = num(maketime(18,30)); // 18:30

//Let vFullDay = num(maketime(10)); // 10 hours - full day



HolidayTable:

LOAD chr(39) & concat(distinct Date,chr(39)&','&chr(39) ) & chr(39) as HolidayList;

LOAD * INLINE [

Date

'01/01/2010'    

'01/01/2011'    

'01/01/2012'    

'01/01/2013'    

'01/01/2014'

'05/04/2010'    

'25/04/2011'    

'09/04/2012'    

'01/04/2013'    

'21/04/2014'

'01/05/2010'    

'01/05/2011'    

'01/05/2012'    

'01/05/2013'    

'01/05/2014'

'08/05/2010'    

'08/05/2011'    

'08/05/2012'    

'08/05/2013'    

'08/05/2014'

'24/05/2010'    

'02/06/2011'    

'17/05/2012'    

'09/05/2013'    

'29/05/2014'

'13/05/2010'    

'13/06/2011'    

'28/05/2012'    

'20/05/2013'    

'09/06/2014'

'14/07/2010'    

'14/07/2011'    

'14/07/2012'    

'14/07/2013'    

'14/07/2014'

'15/08/2010'    

'15/08/2011'    

'15/08/2012'    

'15/08/2013'    

'15/08/2014'

'01/11/2010'    

'01/11/2011'    

'01/11/2012'    

'01/11/2013'    

'01/11/2014'

'11/11/2010'    

'11/11/2011'    

'11/11/2012'    

'11/11/2013'    

'11/11/2014'

'25/12/2010'    

'25/12/2011'    

'25/12/2012'    

'25/12/2013'    

'25/12/2014'



]
;



Let vHolidayList = peek('HolidayList',0,'HolidayTable');



origine:

LOAD *, (Tempsfin + tempsdebut + jourtravail) as durée_travail ;

LOAD recno() as "Record ID",

 
categorie,

    
id as N_mantis,

    
version,

    
severite,     

    
date_changement ,

    
//calcul des heures travaillées sur le jour début

     if ( NetWorkDays(date_changement,date_changement,$(vHolidayList) ) >=1, if ( Hour (date_changement ) + ((Minute (date_changement )/60))<8.5,1,

    
if ( Hour (date_changement ) + ((Minute (date_changement )/60))>= 18.5,0, ((18.5 - (Hour (date_changement ) + ((Minute (date_changement )/60))))/10)) ),0) as tempsdebut,

    
// mise en forme de la date début selon les périodes

     date(monthstart(date_changement), 'MMM-YYYY') AS MoisAnnée,

 
date(Daystart(date_changement), 'DD-MMM-YYYY') as AnnéeMoisJour,

 
dual(week(date_changement)&'-'&WeekYear(date_changement), WeekStart(date_changement)) as SemaineAnnée,

 
// modification des libellés du champ prioritaire 

     if(Prioritaire='Basse', dual('1-Basse', 1),

   
if(Prioritaire ='Normale', dual('2-Normale', 2),

      
if(Prioritaire ='Elevée', dual('3-Elevée', 3)

         )

      )

   )
as Prioritaire,

    
[Etat en cours],

    
Date_fin,

    
// mise en forme de la date début selon les périodes

     date(monthstart(Date_fin), 'MMM-YYYY') AS MoisAnnéedatefin,

 
date(Daystart(Date_fin), 'DD-MMM-YYYY') as AnnéeMoisJourdatefin,

 
dual(week(Date_fin)&'-'&WeekYear(Date_fin), WeekStart(Date_fin)) as SemaineAnnéedatefin,

    
//calcul des heures travaillées sur le jour fin

      if (NetWorkDays(Date_fin,Date_fin,$(vHolidayList) ) >=1, if ( Hour (Date_fin ) + ((Minute (Date_fin )/60))<8.5,0,

   
if (Hour (Date_fin ) + ((Minute (Date_fin )/60))>= 18.5,1, ((((Hour (Date_fin ) + (Minute (Date_fin )/60))-8.5)) /10))),0) as Tempsfin,

     
//calcul des jours travaillés entre la date début et la date fin

      NetWorkDays(date_changement,Date_fin,$(vHolidayList)) as jourtravail,

    
Statut



FROM



(
ooxml, embedded labels, table is Données);





Rvolution:

LOAD id as N_mantis2,

1
as Compteur_Evolution,

    
charge,

    
date_livraison,

    
// mise en forme de la date livraison selon les périodes

     date(monthstart(date_livraison), 'MMM-YYYY') AS MoisAnnée_livraison,

 
date(Daystart(date_livraison), 'DD-MMM-YYYY') as AnnéeMoisJour_livraison,

 
dual(week(date_livraison)&'-'&WeekYear(date_livraison), WeekStart(date_livraison)) as SemaineAnnée_livraison,

    
description



FROM



(
ooxml, embedded labels, table is Evolutions);









Mantis:

LOAD N_mantis ,

 
categorie as Catégorie,

    
severite as sévérité,     

    
date_changement as date_début_mantis,

    
// mise en forme de la date livraison selon les périodes

     date(monthstart(date_changement), 'MMM-YYYY') AS MoisAnnéedate_début_mantis,

 
date(Daystart(date_changement), 'DD-MMM-YYYY') as AnnéeMoisJourdate_début_mantis,

 
dual(week(date_changement)&'-'&WeekYear(date_changement), WeekStart(date_changement)) as SemaineAnnéedate_début_mantis

    
Prioritaire as priorité,

    
[Etat en cours] as Etat,

    
Date_fin as date_fin_mantis,

    
Statut as Statut_courant,

 
durée_travail

RESIDENT origine

where N_mantis <> peek('N_mantis');

//drop table origine;



LEFT JOIN (Mantis) LOAD

N_mantis,

sum(Tempsfin) + sum(tempsdebut) + sum(jourtravail) as durée_travail

resident origine



where Statut = 'Affecté' or Statut = 'Nouveau' group by N_mantis;
Not applicable

Re: input sum of value from another table

I've found it ! yes

Statuts:

LOAD *, (Tempsfin + tempsdebut + jourtravail) as durée_travail ;

LOAD recno() as "Code",

    
id,    

    
date_changement as Date_début ,

    
//calcul des heures travaillées sur le jour début

     if ( NetWorkDays(date_changement,date_changement,$(vHolidayList) ) >=1, if ( Hour (date_changement ) + ((Minute (date_changement )/60))<8.5,1,

    
if ( Hour (date_changement ) + ((Minute (date_changement )/60))>= 18.5,0, ((18.5 - (Hour (date_changement ) + ((Minute (date_changement )/60))))/10)) ),0) as tempsdebut,

    
// mise en forme de la date début selon les périodes

     date(monthstart(date_changement), 'MMM-YYYY') AS MoisAnnéeDate_début,

 
date(Daystart(date_changement), 'DD-MMM-YYYY') as AnnéeMoisJourDate_début,

 
dual(week(date_changement)&'-'&WeekYear(date_changement), WeekStart(date_changement)) as SemaineAnnéeDate_début,

 
Year (date_changement) as AnnéeDate_début,   

    
Date_fin,

    
// mise en forme de la date début selon les périodes

     date(monthstart(Date_fin), 'MMM-YYYY') AS MoisAnnéedatefin,

 
date(Daystart(Date_fin), 'DD-MMM-YYYY') as AnnéeMoisJourdatefin,

 
dual(week(Date_fin)&'-'&WeekYear(Date_fin), WeekStart(Date_fin)) as SemaineAnnéedatefin,

 
Year (Date_fin) as AnnéeDate_fin,

    
//calcul des heures travaillées sur le jour fin

      if (NetWorkDays(Date_fin,Date_fin,$(vHolidayList) ) >=1, if ( Hour (Date_fin ) + ((Minute (Date_fin )/60))<8.5,0,

   
if (Hour (Date_fin ) + ((Minute (Date_fin )/60))>= 18.5,1, ((((Hour (Date_fin ) + (Minute (Date_fin )/60))-8.5)) /10))),0) as Tempsfin,

     
//calcul des jours travaillés entre la date début et la date fin

      NetWorkDays(date_changement,Date_fin,$(vHolidayList)) as jourtravail,

    
Statut



FROM



(
ooxml, embedded labels, table is Données);



Mantis:

LOAD

 
distinct id,

 
categorie as Catégorie,

    
severite as sévérité,

    
version,

 
// modification des libellés du champ prioritaire 

     if(Prioritaire='Basse', dual('1-Basse', 1),

   
if(Prioritaire ='Normale', dual('2-Normale', 2),

      
if(Prioritaire ='Elevée', dual('3-Elevée', 3)

         )

      )

   )
as Prioritaire,

    
[Etat en cours]





FROM



(
ooxml, embedded labels, table is Données);





Evolution:

join (Mantis) LOAD id,

1
as Compteur_Evolution,

    
charge,

    
date_livraison,

    
description



FROM



(
ooxml, embedded labels, table is Evolutions);







LEFT JOIN (Mantis) LOAD

id,

sum(durée_travail) as durée_travail2

resident Statuts



where Statut = 'Affecté' or Statut = 'Nouveau'  group by id;

Community Browser