Discussion :

[nepthys]

J'ai un tableau excel de Clients avec leur date d'inscription et le montant de transactions le jour de l'inscription.

Client	Inscription_Date	TransactionAmount
6580194354	01/09/2016
6591569785	01/09/2016
6580522050	01/09/2016
6580672633	01/09/2016
6590493203	01/09/2016
6590316048	01/09/2016
6580107441	02/09/2016
6580671133	02/09/2016
6580642995	02/09/2016
6592965155	03/09/2016
6590818648	03/09/2016

Je dois remplir la colonne 3 avec le montant de la première transaction effectuée par chaque client le jour de l'inscription (si une transaction n'est pas faite le jour de la transaction, je ne la considère pas).
Pour cela, j'ai une base de données Access avec toutes les transactions client, sur laquelle je fais mes requêtes.

Etape 1 - J'établis la liste de mes clients

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
                                'Je récupère la plage qui contient les clients
                                Set myRange_client = ActiveSheet.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
                                'Pour chaque client
                                For Each cell_client In myRange_client
                                    'Je concatène l'ensemble des MSISDN dans une chaine (attention, la liste commence par une virgule qu'il faut retirer
                                    Listeclient = Listeclient & ",'" & cell_client.Value & "'"
                                 'Je passe à l'inscription suivante pour évluer le montant du cash-in
                                Next cell_client
                                'Je supprime la virgule en début de liste de Pdv
                                Listeclient = Mid$(Listeclient, 2)

Etape 2 : Je fais une requête pour charger un recordset qui contient les transactions des clients

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
 'Je configure la requête SQL
strSql = "SELECT DISTINCT Client, Date, TransactionAmount FROM TransactionsClient WHERE Status Like 'Transaction Success' AND Payee IN (" & Listeclient & ") Order by Date;"
 
'Je lance la requête
Set rs = cn.Execute(strSql)

ETape 3 : Je parcours ma liste de client pour affecter le montant de la transaction qui a été effectuée le jour de l'inscription grâce à une recherche sur mon recordset
Le code m'a été souflé par dysorthographie hier, il marche bien.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
                                For Each cell In myRange_client
                                    rs.MoveFirst
                                    rs.Filter = "[" & rs(0).Name & "] ='" & .Cells(cell.Row, "A").Value & "' AND [" & rs(1).Name & "] ='"&.Cells(cell.Row, "B").Value&&'"
                                    If rs.EOF = False Then
                                        'S'il y a une transaction le jour de l'inscription pour le client en cours, j'inscris le montant dans la ligne
                                        .Cells(cell.Row, "D").Value = rs(2)
                                    Else
                                        'S'il n'y a pas de transaction le jour de l'inscription pour le client en cours, j'inscris 0
                                        .Cells(cell.Row, "D").Value = 0
                                    End If
                                Next cell

L'ensemble du code fonctionne mais il prend plus de 8H.
Est-ce qu'il y a une méthode qui pourrait me permettre d'obtenir un traitement bien plus rapide ?

[Jean-Philippe André]

Salut

la piste a laquelle je pense :
- colle les enregistrements de ton recordset dans un feuille
- mets une serie de formules pour determiner si tu as une match sur les deux champs
- parcourt les lignes et regarde le resultat de la formule.

[nepthys]

Il serait plus rapide de comparer 2 tables entre elles plutôt que de comparer une table et un recordset ?
Est-ce que recourir à des dictionnaires ne pourrait pas aider (je dis cela sans vraiment savoir comment le faire).

[Invité]

Bonjour,

j'ai utilisé Excel mais tu utilisera Access!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Function RqExecute(CLI As String, DTE As String) As Variant
With CreateObject("ADODB.Connection")
    .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"
 With .Execute("select * from [Feuil2$] where [Client]='" & CLI & "' And [Inscription_Date]=#" & Format(DTE, "yyyy-mm-dd") & "#")
    If .EOF = False Then RqExecute = .fields("TransactionAmount")
    .Close
 End With
.Close
    End With
End Function

[nepthys]

Merci, j'ai testé dans ton document cela fontionne bien.
Par contre je n'arrive pas à l'implémenter car la feuille dans laquelle je recherche est située dans un autre worksheet.

Est-ce qu'il y a un moyen de convertir

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

from [Feuil2$]

En

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

from wbToCopy.Worksheets("Feuil2$")

J'ai testé cela ne fonctionne pas

Mea culpa, il fallait remplacer plus haut

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

   .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

   .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & wbToCopy.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"

Je continue et vous tiens au courant.

[Invité]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Function RqExecute(CLI AsString, DTE AsString)AsVariantWith CreateObject("ADODB.Connection")
    .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & wbToCopy.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"
 With .Execute("select * from [Feuil2$] where [Client]='" & CLI & "' And [Inscription_Date]=#" & Format(DTE, "yyyy-mm-dd") & "#")
    If .EOF = False Then RqExecute = .fields("TransactionAmount")
    .Close
 End With
.Close
    End With EndFunction

[nepthys]

J'ai finalisé la macro avec la nouvelle requête, on passe d'une execution en 8H30 à une execution en 3H30, ce qui est une nette amélioration mais reste tout de même très long.

Je me demande s'il ne serait pas possible d'améliorer au niveau du SQL.

Est-ce qu'il est possible en SQL de demander de revoyer un tableau avec des 0 si les valeurs ne sont pas connues.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT DISTINCT Client, TransactionAmount FROM TransactionsClient WHERE Status Like 'Transaction Success' AND Client IN ('6580194354','6591569785','6580522050','6580672633','6590493203','6590316048','6580107441','6580671133','6580642995','6592965155','6590818648') AND Date =#01/09/2016#Order by Date;

A noter que certains clients ne sont pas dans la base TransactionsClient

La requête me retourne

Client	TransactionAmount
6580671133	1000
6580672633	150
6590316048	600
6580522050	200
6592965155	50000

Il manque des lignes (les clients qui n'ont pas de transactions ne sont pas spécifiés) et elles sont dans le désordre (j'aimerais retrouver l'ordre envoyé dans mon IN)

Est-ce qu'une requête serait capable d'y remédier pour me retourner (tous les clients dans le tableau et ils sont dans le bon ordre) :

Client	TransactionAmount
6580194354	0
6591569785	0
6580522050	200
6580672633	150
6590493203	0
6590316048	600
6580107441	0
6580671133	1000
6580642995	0
6592965155	50000
6590818648	0

Si j'arrive à avoir un tel résultat SQL j'aurais juste à coller le contenu dans une feuille et il n'y aura plus de match à faire.

[Invité]

Bonjour,
et si on réduisait encore le temps de traitement?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Sub TEST()
MajTransactionAmount wbToCopy, ThisWorkbook
End Sub
Sub MajTransactionAmount(Source As Workbook, Cible As Workbook)
Cible.Save
With CreateObject("ADODB.Connection")
    .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Cible.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"
    .Execute ("Update  [Feuil1$] as cible inner join (SELECT * FROM [Feuil1$] IN '" & Source.FullName & "'[Excel 12.0;HDR=YES;]) as frm on frm.[Client]= [cible].[Client] AND   frm.[Inscription_Date]= [cible].[Inscription_Date]  set  [cible].[TransactionAmount]=[frm].[TransactionAmount]")
    .Close
End With
End Sub

[nepthys]

Merci.
Je n'arrive pas à l'utiliser.
J'ai joint un code sur la base de ton exemple.
Saurais-tu ce qui bug ?

[Invité]

j'ai pris ton fichier direct et ça fonctionne!

[nepthys]

Effectivement, je ne sais pas ce qui s'est passé.
J'ai relancé le traitement, je te tiens au courant.
Merci.

[nepthys]

Encore une fois merci, on est à 1H40 de traitement (contre 3H30 pour ton précédent code et 8H30 pour le mien).
Cela commence à être acceptable.
Si tu as une autre idée on peut tenter, sinon on peut clore, je tenterai de l'optimiser un poil en SQL.

[Invité]

dans ton premier poste tu parle d'Access quand est il?

la on atteint les limite du possible!

si on se connecte directement en Ado sur Access!!! mais ça sera difficile de faire plus rapide!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Cible.SaveWith CreateObject("ADODB.Connection")    
 .Open "Access"

[nepthys]

dans ton premier poste tu parle d'Access quand est il?

J'ai créé un Post dédié dans la partie Access.

L'idée serait d'avoir le résultat bien formaté en output de ma requête SQL pour la coller directement dans la table Excel cible cible sans avoir à faire une jointure.

[joe.levrai]

Bonjour,

ne serait-il pas possible de rappatrier ta table de transaction au complet, en éventuellement affinant certains critères de date ou de renvoi de la première valeur ?

copier tout ça d'un bloc dans une feuille excel et traiter la récupération de la bonne transaction par formule excel ou VBA ?

[nepthys]

La table fait plusieurs Go, mais dans l'idée c'est ce que l'on fait (avec un filtre sur les clients et sur la date).

[joe.levrai]

C'est donc pas la récupération de la requête qui pose problème, mais ensuite le traitement réalisé sur chaque client ?

J'arrive comme un cheveux sur la soupe, et tu es probablement (non.... "sûrement") avec le plus compétent d'entre-nous sur ta problématique.
Je vais continuer à suivre ce sujet intéressent en silencieux

[nepthys]

C'est donc pas la récupération de la requête qui pose problème, mais ensuite le traitement réalisé sur chaque client ?

En effet, le traitement de jonction est très long.

J'arrive comme un cheveux sur la soupe

Faut tenter, merci tout de même.

[Invité]

je suis allé voir sur la page que tu donnes en lien. perso je préfère continuer sur celle ci! car c'est un pendant de la même question et diviser le sujet c'est diluer les sachant (je m'avance en ce qui me concerne) et les moins sachant (sujet ésotérique) qui seront obligé de glaner les information sur le site!

le trie ne peut ce faire que sur un order by dans ta requête pas sur le array de ta close where! maintenant tu filtre sure Status Like'Transaction Success' ? pour éviter le null?

car dans ta requête tu as toute l'attitude pour tester! iif([TransactionAmount]is null,0,[TransactionAmount])

donc tu fait un sous requête de mise en forme

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select [Client],Format([Inscription_Date],'yyyy-mm-dd'),iff([TransactionAmount] is null,0,[TransactionAmount])

[nepthys]

je suis allé voir sur la page que tu donnes en lien. perso je préfère continuer sur celle ci! car c'est un pendant de la même question et diviser le sujet c'est diluer les sachant (je m'avance en ce qui me concerne) et les moins sachant (sujet ésotérique) qui seront obligé de glaner les information sur le site!

C'est corrigé, j'ai supprimé le message sur la partie Access.


J'ai donc tenté le code proposé dans la console SQL de Access, et la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT Payer, SUM(transactionAmount) FROM TransactionsClient WHERE Status Like 'Transaction Success' AND Payer IN ('80194354','91569785','80522050','80672633','90493203','90316048','80107441','80671133','80642995','92965155','90818648') AND Date >#01/09/2016# Group By Payer;

Donne le même résultat que

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT Payer, iif(SUM(transactionAmount) is null,0,SUM(transactionAmount)) FROM TransactionsClient WHERE Status Like 'Transaction Success' AND Payer IN ('80194354','91569785','80522050','80672633','90493203','90316048','80107441','80671133','80642995','92965155','90818648') AND Date >#01/09/2016# Group By Payer;

Je n'ai pas de ligne contenant 0 si le résultat n'existe pas.