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

brunolelli87 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

cwolf — Thu, 28 May 2020 11:13:51 GMT

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;

topic Re: Is it possible to convert this script into a QlikView Load Script? in QlikView

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

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