Flag-column for each departement

[richters]

Hi all,

I have got a new problem:

Maybe I start with some background information about what I want to do. What I want to do is setting up a benchmark for different departements. For this, I want to build a virtuell departement for each of them (so I have got the same amount of virtuell departements as real ones). To build them, I want to declare the real data for the virtuell ones such as "this row is also in the virtuell departement of sales and this not". We have got the criteria implemented so far.

What we now have is a table like this (excluded unimportant data for this case):

Departement	Row Typ	each x row
Sales	1	4,5
Sales	2	2,735895
Sales	3	1,5
Marketing	1	2
Marketing	2	3,5
Marketing	3	4,8958745

This table is oversimplyfied but I hope I can explain the problem now. There are a lot of different departements with different Row Types. In this combination (Departement + Row Typ) I need every 4,5*x row. Let me give you an example with a greater table to show what my target in the first case would be (There is a column"Case" in the table which is more detailed):

Departement	Row Typ	Case	each x Row	Sales
Sales	1	1	4,5	0
Sales	1	2	4,5	0
Sales	1	3	4,5	0
Sales	1	4	4,5	0
Sales	1	5	4,5	1
Sales	1	6	4,5	0
Sales	1	7	4,5	0
Sales	1	8	4,5	0
Sales	1	9	4,5	1
Sales	1	10	4,5	0

This table shows for the first case "Sales + 1" what I want to have in a new Column. It should name as the departement and show in the row type every row I need as "1" and the others as "0". The information which rows has to be "1" is the column "each x Row" which shows every x row. This of course should happen for every combination of Departement + Row Typ (of course I have got a combination key-field for this)

Does anyone has got an idea how I can implement this in script?

Thank you very much!

Regards,

Michael

[richters]

Hello all,

I have got a first idea of how to deal with it:

FOR i = 1 to NoOfRows('AllDepartementIDs')
    LEt vVariable=FieldValue('DepartementID',$(i));
    LEt vVariable2=FieldValue('DepartementID',$(i))&'T';
    FOR j = 1 to NoOfRows('DepartementData')
        TempTable:
         load
         *,1 as $(vVariable)
         Resident DepartementData
         where $(j) = RowNo();
         trace $(j);
    Next j;
    left join (DepartementData)
    load
         *, 1 as $(vVariable2)
    Resident TempTable;
    drop table TempTable;
Next i;

What I try to do is :

Run through each Departement, and then run through the data for each of them and create a new column which has 0 or 1 in it (now it is just 1 but doesnt matter now).

Then I join the Temp data (with the new column) to the original one and drop it.

The problem now is : It takes a few seconds for EACH of the DepartementData-Rows (which are a few million rows) which I can see by tracing the j-variable. Anyone has got a idea how to do it better (or faster)?

Thank you!