1 Reply Latest reply: Feb 7, 2013 7:50 AM by Sabrina Richter RSS

    Flag-column for each departement

    Sabrina Richter

      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):

      DepartementRow Typ
      each x row
      Sales14,5
      Sales22,735895
      Sales31,5
      Marketing12
      Marketing23,5
      Marketing34,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):

      DepartementRow Typ
      Caseeach x Row
      Sales
      Sales114,50
      Sales124,50
      Sales134,50
      Sales144,50
      Sales154,51
      Sales164,50
      Sales174,50
      Sales184,50
      Sales194,51
      Sales1104,50

      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

        • Re: Flag-column for each departement
          Sabrina Richter

          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!