I need help converting this VBA code into Qlik Sense.
Sub FusionParReferenceEtDonneesUniques()
Dim wsSrc As Worksheet, wsDest As Worksheet
Dim dict As Object, lignesASupprimer As Collection
Dim refKey As Variant, i As Long
Dim codeFull As String, codeNum As String
Set wssrc=ThisWorkbook.Sheets("NOTILUS_MISS")
Set wsDest = ThisWorkbook.Sheets("Sheet1")
Set dict = CreateObject("Scripting.Dictionary")
Set lignesASupprimer = New Collection
Dim lastRowDest As Long: lastRowDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRowDest
If UCase(Trim(wsDest.Cells(i, "A").Value)) = "MISSION/COMPTABILISATION/PAIEMENT" Then
codeFull = wsDest.Cells(i, "J").Value
codeNum = ExtractNumber(codeFull)
If codeNum <> "" Then
If Not dict.Exists(codeNum) Then
Set dict(codeNum) = CreateObject("Scripting.Dictionary")
dict(codeNum)("Row") = i
dict(codeNum)("MontantTotal") = ToDouble(wsDest.Cells(i, "M"))
Else
dict(codeNum)("MontantTotal") = dict(codeNum)("MontantTotal") + ToDouble(wsDest.Cells(i, "M"))
lignesASupprimer.Add i
End If
End If
End If
Next i
For Each refKey In dict.Keys
Dim rowCible As Long: rowCible = dict(refKey)("Row")
wsDest.Cells(rowCible, "I").Value = wsDest.Cells(rowCible, "K").Value
wsDest.Cells(rowCible, "M").Value = dict(refKey)("MontantTotal")
Next refKey
For i = lignesASupprimer.Count To 1 Step -1
wsDest.Rows(lignesASupprimer(i)).Delete
Next i
End Sub
I would like to reproduce this logic using FirstValue().
So far, I have created three tables:
NoConcatenate
WS_Mission:
LOAD
*,
KeepChar([codeFull], '0123456789') AS codeNum,
[Montant] AS MontantNum
RESIDENT WS_Final
WHERE NOT (
Upper([Rub]) = 'MISSION/COMPTABILISATION/PAIEMENT'
AND Len([codeFull]) > 0
);
NoConcatenate
WS_Mission_1:
LOAD
*,
KeepChar([codeFull], '0123456789') AS codeNum,
[Montant] AS MontantNum
RESIDENT WS_Final
WHERE Upper([Rub]) = 'MISSION/COMPTABILISATION/PAIEMENT'
AND Len([codeFull]) > 0;
NoConcatenate
Mission_Agreg:
LOAD
codeNum,
Sum(MontantNum) AS MontantTotal
RESIDENT WS_Mission_1
GROUP BY codeNum;
At this point, I’m not sure how to:
remove duplicates (keeping one row per codeNum)
apply FirstValue() logic
and perform the correct joins to rebuild the final table
You'll need to update some paths within the code prior to running but I've included comments to assist.
Key translation decisions explained
Removing duplicates → GROUP BY + FirstValue()
Your VBA keeps the first row it encounters and deletes the rest. In Qlik that's
FirstValue(ColK)AS ColK_First
FirstValue() returns the value from whichever row was loaded first for that CodeNum group — same behavior as your dictionary storing only the initial row index.
Summing MontantTotal → Sum(Montant)
The VBA accumulates dict(codeNum)("MontantTotal") += ... across duplicates. Sum(Montant) grouped by CodeNum is the direct equivalent.
ColI ← ColK replacement
Your macro does wsDest.Cells(rowCible, "I").Value = wsDest.Cells(rowCible, "K").Value on the surviving row. In the script, ColI is simply aliased from ColK_First — no patch-after-the-fact needed.
ExtractNumber() → PurgeChar()
Since I don't have the source of ExtractNumber, I used PurgeChar() to strip all non-digit characters. If your codes follow a more specific pattern (e.g. always 6 digits after a slash), replace with:
Mid(J,Index(J,'/')+1,6)AS CodeNum
What you'll need to adjust
Item What to change lib://YourDataConnection/... Your actual Qlik data connection name table is NOTILUS_MISS Confirm sheet name matches exactly PurgeChar(...) regex Match whatever ExtractNumber() actually does Column letters A/I/J/K/M If your load uses named fields, replace with actual field names
Function ExtractNumber(str As String) As String Dim i As Long, result As String result = "" For i = 1 To Len(str) If Mid(str, i, 1) Like "#" Then result = result & Mid(str, i, 1) End If Next i ExtractNumber = result End Function
Personally I would start with a different approach by loading the data from a resident table to ensure a defined sorting of the records to be able to apply interrecord-functions to number them in regard to the needed keys. And within this the n conditions are resolved in n flags. This may look like:
load *, rowno() as RowNo where Flag; load *, if(X = 'a' and Y = 'b', true(), false()) as Flag; load *, recno() as RecNo, if(MyKey = previous(MyKey), peek('Nr') + 1, 1) as Nr, keepchar(Code, '0123456789') as X, purgechar(Code, '0123456789') as Y, if(Field1 = 'x', 1, 0) as A, if(Field2 = 'y', 1, 0) as B resident PreLoad order by MyKey;
It's a simplified demo of the logic to show how to track the source + target records as well as the in between numbering of a key-field (it's also possible against n fields but often it's simpler to combine them in beforehand). Also n conditions could be included in one of n preceding load-layer to flag and/or filter the records which may also include in the end the Nr = 1.
Even expensive and complex matching and filtering logic are quickly developed - step by step expanding to the next part.
