Hi Everyone,
The following is a piece of VB code that our Excel Sheet uses to calculate some fields.
Do While i <= UBound(arr1)
j = iLineRef
bFound = False
'Loop into records related to the selected SIRE ID
Do While curID = arr1(j, SIRE_COLUMN_ORDER.COL_SIR2)
'On Error Resume Next
If arr2(j, 1) = arr1(i, SIRE_COLUMN_ORDER.FM_QPhase) Then 'Check the SIRE state (in order to cumul all records with the same status - for the current SIRE- into one cell)
If Err.Number = 0 Then
arr2(j, 1) = arr1(i, SIRE_COLUMN_ORDER.FM_QPhase) 'Set the GlobalState column
If arr2(j, 1) = "BusinessTest" Then
If arr1(i - 1, 17) <> "BusinessTest" Then
arr2(j, 2) = arr1(i, SIRE_COLUMN_ORDER.FM_Days) 'Set the GlobalDays column
Else
'Only last testing period is counted
arr2(j, 2) = arr2(j, 2) + arr1(i, SIRE_COLUMN_ORDER.FM_Days) 'Set the GlobalDays column
End If
Else
arr2(j, 2) = arr2(j, 2) + arr1(i, SIRE_COLUMN_ORDER.FM_Days) 'Set the GlobalDays column
End If
bFound = True
Exit Do
End If
End If
j = j + 1
If j > UBound(arr1) Then Exit Do
Loop
'If state doesn't exist yet, create a new GlobalState
If bFound = False Then
arr2(i, 1) = arr1(i, SIRE_COLUMN_ORDER.FM_QPhase)
arr2(i, 2) = arr2(i, 2) + arr1(i, SIRE_COLUMN_ORDER.FM_Days)
End If
Loop
I am trying to translate it into QlikView, this is what I have come up with:
temp_global_days:
LOAD
RS_SIRE,
RS_QPhase,
if(RS_QPhase = 'BusinessTest', max(RS_Days), sum(RS_Days)) AS RS_GlobalDays
Resident RawDataState
Group By RS_SIRE, RS_QPhase;
The QlikView version works, however some values are not calculated 100% correctly in QlikView. I am not sure how to proceed. Any ideas or help be very apprecaited!
Thanks,
-Marek
