topic Re: Macro in Loop in QlikView

Macro in Loop

Anonymous — Thu, 30 Mar 2017 12:59:42 GMT

Hi All,

Need some suggestions.

I have a macro to update a date in an excel.

But As I need to run the code in a loop for each day, the excel file which I have is too huge that it takes a lot of time to update a single value. So if I am running it for 1 year it will run forever.

Any ideas about getting better performance, like opening file one time for each month and performing it for all the days and closing it . and same.

Hope I am clear.

Thanks in advance.

Re: Macro in Loop

zhadrakas — Thu, 30 Mar 2017 13:14:48 GMT

hi,

macro execution work single threaded, so you have bad Performance in General.

Would it be better to go this way?

1] Load the data you want into qlikview

2] Transform, Update your date, etc.

3] Store it into Excel or CSV

this can be helpful

Can I use the script to store into xlsx?

regards

tim

Re: Macro in Loop

Anonymous — Thu, 30 Mar 2017 13:39:03 GMT

Thanks for your feedback Tim,

But the issue here is different.

in excel, if i update the date, the value in another cell changes. Lot of calculation is implemented for that. So I ned to read that value based on the date. So changing the date in loop and reading.

Re: Macro in Loop

Peter_Cammaert — Thu, 30 Mar 2017 14:34:13 GMT

If the date-to-modify resides in a single column, maybe let QV read the dates, transform them according to the rules you specify and write them to csv in the exact same order as the original cells. Then simply copy/paste the new values in the orgiinal column.