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

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;

1 Reply

Re: QVD Problem

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


talk is cheap, supply exceeds demand
Community Browser