Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
richters
New Contributor III

Flag-column for each departement

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

1 Reply
richters
New Contributor III

Re: Flag-column for each departement

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!

Community Browser