Qlik Community

QlikView Extensions

Discussion Board for collaboration on QlikView Extensions.

jstanek1
New Contributor

Calculated dimension

Hello,

Im dealing with current problem, hopefully someone will have some idea I got input data like this:

   

MonthUserNumber of sales
IXA12
XA15
XIA19
XIIA11
XIB7
XIIB14
XIIC10

The usual table output would be:

    

IXXXIXII
A12151911
B 714
C 10

But I would need:

    

1234
A12151911
B714
C10

Basicly I need to compare employees based on number of months they are with company. I think calculated dimension is way to go as far as the input table is not the simple as the above one :-) Its made from more tables & their connections... Any hints?

1 Solution

Accepted Solutions
felipedl
Valued Contributor III

Re: Calculated dimension

Hi Jaroslav,

You'll have to do some script transformations to get what you need, by getting the minimum month for each user (I also changed the roman literal month number into a integer number):

dummy:

Load * Inline

[

Month,User,Number of sales

9,A,12

10,A,15

11,A,19

12,A,11

11,B,7

12,B,14

12,C,10

];

Max:

load

User,

min(Month) as minMonth

Resident dummy

group by User;

left join (dummy)

Load

User,

minMonth as Month,

1 as [Num]

Resident Max;

NoConcatenate

data:

Load

User,

Month,

[Number of sales],

if (User = peek(User), peek(Num)+1,1) as Num;

Load

*

Resident dummy

order by User;

drop table dummy, Max;

The following code gets me both of your tables above:

Sample.png

Attached the QVW I used.

2 Replies
felipedl
Valued Contributor III

Re: Calculated dimension

Hi Jaroslav,

You'll have to do some script transformations to get what you need, by getting the minimum month for each user (I also changed the roman literal month number into a integer number):

dummy:

Load * Inline

[

Month,User,Number of sales

9,A,12

10,A,15

11,A,19

12,A,11

11,B,7

12,B,14

12,C,10

];

Max:

load

User,

min(Month) as minMonth

Resident dummy

group by User;

left join (dummy)

Load

User,

minMonth as Month,

1 as [Num]

Resident Max;

NoConcatenate

data:

Load

User,

Month,

[Number of sales],

if (User = peek(User), peek(Num)+1,1) as Num;

Load

*

Resident dummy

order by User;

drop table dummy, Max;

The following code gets me both of your tables above:

Sample.png

Attached the QVW I used.

jstanek1
New Contributor

Re: Calculated dimension

Thank you Felip! I think I can make it working...