Using Preceding load for this example

[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;

[zied_ahmed1]

rahulpawarb‌ stalwar1‌

[prieper]

... and what is your question/problem?

[zied_ahmed1]

I need to use Preceding load to load one time the data and display the three STATUTs without repeating the same code

[tamilarasu]

Hi Zied,

Why do you need preceding load? You can simply filter the data in where clause itself like below.

Let vToday = Today();      // Make sure the date format is correct

Datas:

load

      date,

      car as car,

      mat ,

      Tax,

      'ON'  as STATUT,

      count(Statut_sup) as nbr

      resident table1

      where Statut_sup='Actif' And date = '$(vToday)'

      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' And date = '$(vToday)'

      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' And date = '$(vToday)'

      group by  date,

      car,

      mat;

drop table table1;

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

Concatenate

    Load *

        resident Fact ;

drop table Fact;

[zied_ahmed1]

Hello Tamil,

I need to minimize the code, if you can see the three STATUT with concatenate i think i can use them in just one small code what do you think ?

[vinieme12]

i don't even understand why we are concatenating three tables? this should be enuf

   load

       date,

          car as car,

         mat ,

         Tax,

         Pick(Wildmatch(STATUT,'Actif','Inactif','Bloquée'),'ON','OFF','Bloquée') as STATUT

         count(Statut_sup) as nbr

         resident table1

         group by

        date_flotte,

         Taux,

         //[%Date ID],

         car ,

         mat;

or

temp:

load

       date,

          car as car,

         mat ,

         Tax,

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

         resident table1;

drop table table1;

noconcatenate

Load

date,

car,

         mat ,

         Tax,

     STATUT

Count(STATUT) as nbr

Resident temp

Group by

date,

car,

         mat ,

         Tax,

     STATUT;

[tamilarasu]

Zied - You are using different field names in first group by clause also "Tax" Field is missing in second and third load. and you are adding "Pla" field in second load. If you could confirm the field names are same in three loads and group by clause field names are same then we can try to simply the code.

[zied_ahmed1]

Hello Tamil,

Yes i can use the same group by for the three load

Regards

[zied_ahmed1]

Hello Vineeth,

I used the first code script but the error was with the STATUT :

Pick(Wildmatch(STATUT,'Actif','Inactif','Bloquée'),'ON','OFF','Bloquée') as STATUT

i think because we use it before we declare it ...