Is it possible to convert this script into a QlikView Load Script?

[brunolelli87]

Hello guys,

I'm here again with a brand new doubt. I'm currently downloading an excel file, and converting it to my needs using the following simple Excel VBA script. After that, I'm loading it into QlikView.

My question is:
Is it possible to convert the following script into a QlikView Script?

Please find below my Excel VBA Script:

Sub Convert()

Dim linF As Integer
Dim linI As Integer
Dim linD As Integer

linI = 4    'The first row with data on my Excel File
linD = 4    'The first row where I want my new Data on Excel
linF = Range("A4").End(xlDown).Row - 5   'Find row many available rows with data I have on the very first column of my excel 

'Execute the following code for the number of available data that I have.
For i = 1 To linF

'Compare the date of the first row with the date of the next one
DataIni = Cells(linI, 1).Value
DataTo = Cells(linI + 1, 1).Value

'Do each loop comparing both dates
Do While DataIni < DataTo

    Cells(linD, 7).Value = DateAdd("d", 1, DataIni)
    Cells(linD, 8).Value = Cells(linI, 2).Value
    DataIni = Cells(linD, 7).Value
    linD = linD + 1

Loop

linI = linI + 1

Next i

'Add 60 days at the end.
For j = 1 To 60

    Cells(linD, 7).Value = DateAdd("d", 1, DataIni)
    Cells(linD, 8).Value = Cells(linD - 1, 8).Value
    DataIni = Cells(linD, 7).Value
    linD = linD + 1
    

Next j

End Sub

Please, find attached my Excel VBA File for your reference.

My QlikView application is using the data from Columns G and H!

Thanks,
Bruno Lelli

[cwolf · Accepted solution]

You can get this with an IntervalMatch:

t1:
LOAD
*
Where not IsNull(Índice);
LOAD A as Mês,
     B as Índice
FROM [IGP-DI.xlsm](ooxml, no labels, header is 3 lines, table is Plan1);

IGP_DI:
load
Mês,
Índice,
Mês as From,
if(IsNull(Previous(Mês)),Mês+60, Previous(Mês)-1) as To
Resident t1
Order By Mês desc;

drop table t1;

t1:
LOAD 
Min(From) as Start,
Max(To) as End
Resident IGP_DI;

let vStart= FieldValue('Start',1)-1;
let vEnd= FieldValue('End',1);

drop table t1;

t1:
LOAD
RecNo() + $(vStart) as Date
AutoGenerate $(vEnd)-$(vStart);

t2:
IntervalMatch(Date)
LOAD Distinct
From,
To
Resident IGP_DI;

drop field Mês;

Inner Join (IGP_DI)
LOAD Distinct
From,
To,
Date(Date) as Mês
Resident t2;

DROP Fields From,To;
DROP Tables t1,t2;