convert VBA logic to Qlik Sense

[rbeb]

Hello team,

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

Any guidance would be greatly appreciated.

Thank you!

[WeLoveQlik]

Please try the attached code.

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

[rbeb]

hello

ExtractNumber is a function

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

[marcus_sommer]

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.