Excel VB Script to QlikView - Qlik Community

Report Inappropriate Content · ‎2012-03-05

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

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