Create data with out looping

[#1]

Hi All,

We are having a table with two dates. We need to create the records based on the two Dates  to do certain calculation.

In Table A I have given the data we are having currently.

In Table B I have given the data we need. Currently we are writing a loop to achive it and it is taking 3 hours as the number or records are more. Please advise.

Sample QVW attached.

Thanks,
Sijo

[flipside · Accepted solution]

What type of loop are you using at the moment? I think you'll have to use some kind of loop if you really need the data creating in the dataset. A While loop should be the fastest way ...

LOAD [Sl No],
[Inception Year]+iterno()-1 as [Inception Year],
[Closed Year]
FROM
[sample.xls]
(biff, embedded labels, table is Sheet1$)
while [Inception Year]+iterno()-1<=[Closed Year];

LOAD [Sl No],
[Inception Year],
[Closed Year]
FROM
[sample.xls]
(biff, embedded labels, table is Sheet1$)
where [Inception Year]>[Closed Year];

If you're not using this method at the moment, try it and see if there is an improvement. (I've used a separate load to bring in single row data where Inception Year is greater than Closed Year, and you'll need to change back to ooxml format).

flipside

[#3]

Thanks