Use preceding load with group by

[zied_ahmed1]

I would like to use Preceding Load with this code :

LOAD

    * WHERE  "date"= Today() ;

    load

       date,

          car as car,

         mat ,

         Tax,

         'ON'  as STATUT,

         count(Statut_sup) as nbr

         resident table1

         where Statut_sup='Actif'

         group by

        date_flotte,

         Taux,

         //[%Date ID],

         car ,

         mat;

         Concatenate 

           load

      Pla,

        date,

         car as car,

         mat,

           'OFF'  as STATUT,

         count(Statut_sup) as nbr

         resident table1

         where Statut_sup='Inactif'

         group by  date,

         car,

         mat,

Pla;

          Concatenate 

           load

       date,

         car as car,

         mat,

          'Bloquée'  as STATUT,

         count(Statut_sup) as nbr

         resident table1

         where Statut_sup='Bloquée'

         group by  date,

         car,

         mat;

drop table table1;

    STORE [FlotteFinal] into [$(vQvdPath)\QVD_final.qvd](qvd);

          Concatenate

             Load*

             resident Fact ;

             drop table Fact;

[Anonymous]

preceding load works only on 1 load....

if you use concatenate you have more than 1 load.

So this doesn't work. You'll have to use resident load.

[zied_ahmed1]

I was thinking that i can Load this one time :

car as car,

         mat ,

         Tax,

                count(Statut_sup) as nbr

         resident table1

         where Statut_sup='Actif'

         group by

        date_flotte,

         Taux,

         //[%Date ID],

         car ,

         mat;

after that i add just this   'ON'  as STATUT,  'OFF'  as STATUT , 'Blocke'  as STATUT

[Anonymous]

does that work without preceding load???:

load

       date,

          car as car,

         mat ,

         Tax,

         'ON'  as STATUT,

         count(Statut_sup) as nbr

         resident table1

         where Statut_sup='Actif'

         group by

        date_flotte,

         Taux,

         //[%Date ID],

         car ,

         mat;

[Digvijay_Singh]

If your group by  is same in all loads, you can check and update STATUT in one statement -

Pick(Match(Statut_sup, 'Actif','Inactif','Bloquée',),'ON','OFF','Blocke' ) as STATUT

[zied_ahmed1]

yes yes i copied it wrong date_flotte is date and Taux is Tax

[zied_ahmed1]

this is interesting...for the Load what i do exactly

[Anonymous]

then it should work...

What's the problem then?, do you get an empty table?!?

(I don't know if your datefield is a dual() datefield and I would suggest always using today(1) instead of today() within script.)

[zied_ahmed1]

it's doesn't work like this :

Flotte4:

    load

    Plaque,

    statut_actif,

    date_flotte,

    statut_inactif,

    statut_bloque,

    statut_endom,

    //Statut_sup,

    Plaque_flotte as Plaque_flotte,

    Materiel_flotte,

    Taux,

                 if (Statut_cond=1,'Actif',

                (if (Statut_cond=2,'Bloquée',

                (IF(Statut_cond=3,'Inactif'))))) as Statut_sup

    Resident Flotte3;

    drop table Flotte3;

//pour SR sans traversée

    NoConcatenate

    FlotteFinal:

   //chargement de QVD qui contient les données des jours qui précedent

     LOAD

     //autonumber( date(date_dossier, 'DD/MM/YYYY'),'%Date ID')  as [%Date ID],

     date_flotte,  

     Plaque_flotte,

     Materiel_flotte,

     Taux,

     Plaque  FROM [$(vQvdPath)\Vectorys_final.qvd] (qvd) ;//source des QVDs historisés

Concatenate

//Concatenation du QVD avec les données d'ajourd'hui

     LOAD

    * WHERE  "date_flotte"= Today();

   // STORE [Test] into [$(vQvdPath)\Vectorys_Today.qvd](qvd);

           load

           Plaque,

       date_flotte,

       Plaque_flotte as Plaque_flotte,

       Materiel_flotte,

      Pick(Match(Statut_sup, 'Actif','Bloquée'),'ON','Bloquée' ) as STATUT

       resident Flotte4

         group by 

         Plaque,

         date_flotte,

         Plaque_flotte,

         Materiel_flotte;

drop table Flotte4;

[Digvijay_Singh]

I think you are still using preceding load with concatenate, which might be the reason for not working. What error you are receiving?

You may try

          having "date_flotte"= Today() along with group by clause to get rid of preceding load, not sure though.