Creating a Single Dimension from Multiple fields

[Anonymous]

Hello All,

Can I create a single dimension from multiple fields in Qlikview?

Example:

Say I am having 3 fields in a table with name like SickLeave, CasualLeave, EarnedLeave.

Now I have to create a single dimension of these three fields say LeaveBalance.

Is it possible?

Regards,

Balraj

[maxgro]

see attachment, hope to understand your req

[jagan]

Hi Balraj,

Try CrossTable() like below

Table:

CrossTable(LeaveType, LeaveCount)

Load EMPID,

SickLeave,

CasualLeave,

EarnedLeave

From DataSource;

Hope this helps you.

Regards,

Jagan.

[jagan]

Or Try like below

Data:

Load EMPID,

'SickLeave' AS LeaveType,

SickLeave AS LeaveCount

From DataSource;

Concatenate(Data)

Load EMPID,

'CasualLeave' AS LeaveType,

CasualLeave AS LeaveCount

From DataSource;

Concatenate(Data)

Load EMPID,

'EarnedLeave' AS LeaveType,

EarnedLeave AS LeaveCount

From DataSource;

Regards,

jagan.

[rustyfishbones]

Did you try using Valuelist as a Calculated Dimension

=ValueList('SickLeave', 'CasualLeave', 'EarnedLeave')

Regards

Al

[Anonymous]

Thanks Jagan,

Could you help me to perform accumulation for same in pivot table?

Say, a employee X have 10 SickLeave for a year. Now it should accumulate throughout the year if he takes any SickLeave in a month.

If he takes 1 SL in January then in Feb. balance should be 9 and if he takes 2 SL in Feb. then For March Balance should be 7 and so on....

Can I do it in pivot table?

Regards,

Balraj

[jagan]

Hi,

Can you attach some sample data, so that it is easier to understand and give solution.

Regards,

Jagan.

[Anonymous]

Jagan,

I am having following fields: EmpName, LeaveMonth, Total_CasualLeave, Total_SickLeave(Predefined), LeaveCode, LeaveDays etc.

Now I have to generate a Report(Need_To_Achieve Sample is attached) which will show a leave balance of a employee on daily basis.

I have calculated following fields:

Accumulated CasualLeave= sum(Total_CasualLeave), same for rest.

CL Accrued= if(LeaveCode='CL', sum(LeaveDays), '0')

Closing CL Balance= sum(Total_CasualLeave) - if(LeaveCode='CL', sum(LeaveDays), '0')

Now what I want, my new daily transaction shuold start with the Closing Balance w.r.t Faculty because if some body taking a leave then it will be deducted from the Accumulated Leave Balance.

I am new to Qlikview, so do not know which function I should use at expression level or script level to achieve this in Qlikview.

Need your help buddy.

Regards,

Balraj