Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Switching from key figure to account model

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!

10 Replies
jason_michaelid
Honored Contributor II

Switching from key figure to account model

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

Not applicable

Re: Switching from key figure to account model

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_michaelid
Honored Contributor II

Switching from key figure to account model

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

Re: Switching from key figure to account model

Hi,

Sory to reponse you late.

See the sample file and attachements

HTH

Rgds

Anand

Not applicable

Re: Switching from key figure to account model

Hi,

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

See the example i posted

C u,

Stefano.

Not applicable

Re: Switching from key figure to account model

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

Not applicable

Re: Switching from key figure to account model

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_michaelid
Honored Contributor II

Switching from key figure to account model

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.

Not applicable

Switching from key figure to account model

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.