Switching from key figure to account model

[#1]

Hello everyone,

i'm a newcomer with qlikview and have my first problem. I try to load a file with many key figure columns. For example:

• Count of members older 0
• Count of members older 20
• Count of members older 40

Now this is not very helpful when I want to report my count of members with age as dimension, because I don't have this dimension. How can I transform this model now to account model? For example:

• Age: [older 0, older 20, older 40]
• Count of members

Thank you very much!

[Jason_Michaelides]

Can you post some sample data? Not quite sure what you mean...

[#3]

Hey,

thanks for your response. I've attached an excel-workbook with two sheets. The first sheet contains the data table and the second sheet shows the diagram.

[Jason_Michaelides]

Ok - I think I get it now!

You need to use the Crosstable() function.  Look it up in the F1 help and the reference manual for the explanation but the load script should look something like this:

Data_temp:

LOAD

  *

FROM ExcelFile...;

CROSSTABLE(Age,CountOfMembers,4)

LOAD

   *

RESIDENT Data_temp;

DROP TABLE Data_temp;

Try that - hope it helps.

Jason

[its_anandrjs]

Hi,

Sory to reponse you late.

See the sample file and attachements

HTH

Rgds

Anand

[#6]

Hi,

i have understood u can use class function also or "aggr" function.

See the example i posted

C u,

Stefano.

[#7]

Can you send me the script code please. I've at the moment only ther personal edition installed.

[#8]

hi,

look if the result is ok for u.

the script is simple:

LOAD * INLINE [

    name, age

    a, 20

    b, 30

    c, 33

    d, 12

    r, 40

    e, 50

    g, 60

    h, 10

];

the dimension of 1st table is:

=class(age,20)

the dimension of 2nd table is:

=if(aggr(only(age),name)<=20,'<=20',

          if(aggr(only(age),name)>20 and aggr(only(age),name)<=40,'>=20 <=40','over'))

the expression is the same:

count(name)

Stefano.

[Jason_Michaelides]

The problem with hardcoding the age ranges in your expressions is you have to update them all each time the data changes and introduces a new age.  crosstable will always account for however many extra columns are added to the data source.

[#10]

Hi Jason,

this is not true if u have a static range of age.

And the  calculated dimension of the 1st table creates rows automatically.

I don't know if your solution works but it is so we have 3 possibility...

The QlikView Ways are infinite..

Stefano.