Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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);