With Internal Loop, a counter increments cell A1 and the worksheet executes functions until an error occurs. You can import data lists easily in this manner. The exercise below demonstrates how this process works.
We start by adding the region “Russia” in the Modeler. Do not worry about consolidating it to “Europe”; right now we are only interested in demonstrating the functionality.
Then open the “testimport.txt” file using Excel. This in the following folder as a default: C:\Users\Public\Documents\Jedox\Jedox Suite.
You have to make a few changes in the document. First, save the file under the name “Russia.xlsx”. Proceed as follows:
- Select column A, and insert a blank column.
- Select rows 1 and 2, and insert blank rows.
- Starting at cell B59 delete all following rows.
- Change the text from “Austria” to “Russia” in column B: Select the area B3:B58, and then press CTRL+H. Enter the text, and click “Replace all”.
- Change the text from “Planned” to “Budget” in column F: Select the area F3:F58, and then press CTRL+H. Enter the text, and click “Replace all”.
- Change the month names to English spelling in column E. Using the method just described, replace “Mrz, Mai, Okt and Dez” with “Mar, May, Oct and Dec” in E3:E58.
Save the file again. You should now have a file with a total of 56 data records starting with cell B3. Consequently, the loop must end after the 56th data record has been processed or when the 57th data record is read.
The “Russia.xls” file now appears as follows (excerpt):
Create the following view with “Paste View” in a blank Excel worksheet: drag the “Products” into the Row titles, and select the notebooks displayed above.
Transfer “Months” into the Column titles, and select the individual months. After the pasting, please also switch “Europe” to “Russia” and “Versions” to “Budget” (under “Variance”). This makes it possible for you to check how the loop is processed.
Return to the Russia.xls file, then go to cell B1 and insert the following function:
Insert in C1:
Insert in D1:
Insert in E1:
Insert in F1:
Insert in G1:
Insert in H1:
We are using here the Excel function OFFSET() in the cells B1 to H1 to return the reference values of a defined area. Its form is OFFSET(reference,rows,cols). Starting with cell B3 as the reference, the OFFSET function will choose the row that is the result of the start reference + the current value of A1-1. Because A1 is still currently blank, the result = “DIV/0”. The value behind “$A$1-1” states whether the start cell is in cell B3 or a column moved by “X”.
The formula in cell B1 is: =If(OFFSET($B$3,$A$1-1,0)<>””,”Please wait…”,10/0)
The IF query checks whether the value in B3 is blank (<>””). As long as B3 is not empty, B1 displays “Please wait…”. Otherwise an error is evoked in B1, because 10 divided by 0 results in “#DIV/0”, and with this error the process is aborted.
The formula in cells C1 to H1 states (using the example of D1): =OFFSET($B$3,$A$1-1,1)
This formula is identical for all cells. The only difference is in the last part of the formula. Here you define whether the reference value should be taken from a column moved by “X”. Consequently, the formula states:
Start reference = $B$3
Counter = $A$1-1 = move the start reference by the number resulting from A1-1
Return: the column (+1), which follows the start reference + counter, i.e. column “C” in this case.
Russia.xls now appears as follows:
Cell I1 should contain the formula that writes your data into the “Sales” cube. You are already familiar with the procedure.
Insert a PALO.SETDATA formula in cell “I1” using Paste Data Function.
The following formula results (see formula bar):
Now replace “value” with H1, “All Products” with D1, “Europe” with C1, “Year” with F1, “2002” with E1, and “Variance” with G1.
As long as cell A1 is still blank, the formulas in the area B1:I1 return errors messages or “0”. If you enter “1” in A1, you obtain the following:
If the OFFSET function is executed in C1 to H1, you obtain the values of B3 to G3. If you enter “2” in A1 instead, you obtain the values B4 to G4.
Importing in Jedox
Of course, it would not be very convenient if you now had to enter all values from 1 to 56 manually into cell A1. Delete the value entered in cell A1, and start the Import Wizard. Then select “Internal Loop” and click Next. You will get the following view:
The Data Import Wizard has entered 1 in A1. If you click Continue, you will see the individual rows being imported. After you have verified that everything is being imported by clicking on Next a few times, hit Finish. The function in I1 writes the values in the addressed cell cube.
The import ends when 57 appears as your value in A1. B1 with “DIV/0” ensures that the process is aborted.
Take a look at the overview that you created in a blank worksheet earlier. You can see that all 56 data records have been imported into the Jedox cube.