1 Reply Latest reply: Nov 4, 2013 6:04 AM by Gysbert Wassenaar RSS

    QVD Problem

      Hello

       

      Monthly i receive a Xls File with a lot of information abour costomers ,  so the goal is to set up a pivot table or reference table to add juste the new entries , to have a message like that : for example ( for octobre 4 customers  are be add , for septembre 5 cust are been added ...)

       

      for this

       

      I use a concatenation of tables + qvd records

       

      1st stage: I load my table reference

       

      2nd stage: a month later, I reload the second example I have 2 new customers

       

      3rd stage: a month later, I reload the table I get and I realize that I have 4 new customers , basically, he can not manage to save the last value, he  reload and show me all news entries

       

      Someone have a idea to solve my problem Thank U

       

      Signalitique_reference:

      LOAD

        [ref matricule],

        [ref id ss],

           [ref nom],

        [ref prenom],

        [ref Créé le]

       

      FROM $(FilePath)\QV_Dev\Signalitique_reference.QVD (qvd);

       

       

       

       

       

      //Utile 1 fois pour iniatialiser le projet

       

       

      //Signalitique_reference:

      //LOAD

      // @1 as [ref matricule],

      // @2 as [ref id ss],

      //    @6 as [ref nom],

      // @7 as [ref prenom],

      // '$(ProcessTime)' as [ref Créé le]

      //FROM [$(FilePath)\data\Signalitique PS\24 - SIGNALETIQUE GROUPE*.xls]

      //(biff, no labels);

      //

      //

      //

      //STORE Signalitique_reference INTO $(FilePath)\QV_Dev\Signalitique_reference.QVD;

       

       

      NOCONCATENATE

      LOAD

        @1 as [ref matricule],

        @2 as [ref id ss],

           @6 as [ref nom],

        @7 as [ref prenom],

        '$(ProcessTime)' as [ref Créé le]

      FROM [$(FilePath)\data\Signalitique PS\24 - SIGNALETIQUE GROUPE*.xls] // pourquoi on refait ca alors qu'on a déja chargé les champs dans un siganlitique_ref.qvd

      (biff, no labels);

       

      STORE RefSign INTO $(FilePath)\QV_Dev\RefSign.QVD;

       

       

      drop table RefSign ;

       

      LET vOldRecords = QvdNoOfRecords('$(FilePath)\QV_Dev\Signalitique_reference.QVD');

       

      CONCATENATE (Signalitique_reference) LOAD Distinct

        *

      FROM $(FilePath)\QV_Dev\RefSign.QVD (qvd)

      WHERE NOT Exists([ref matricule]);

       

       

      LET vNewRecords = NoOfRows('Signalitique_reference') - $(vOldRecords);

       

       

      STORE Signalitique_referenceINTO $(FilePath)\Dev\Signalitique_reference.QVD;

        • Re: QVD Problem
          Gysbert Wassenaar

          First load the old records, then load the new records and use not exists to check if a customer is new and if it is fill a field with a date of it's first occurence. Perhaps something like this:

           

          LOAD

            @1 as [ref matricule],

            @2 as [ref id ss],

               @6 as [ref nom],

            @7 as [ref prenom],

            '$(ProcessTime)' as [ref Créé le]

            if(not exists(@1,[ref matricule]), monthstart(today())) as MonthFirstOccurence

          FROM [$(FilePath)\data\Signalitique PS\24 - SIGNALETIQUE GROUPE*.xls]

          (biff, no labels);