topic Re: Creating a Single Dimension from Multiple fields in QlikView

Creating a Single Dimension from Multiple fields

Anonymous — Thu, 14 Aug 2014 07:31:26 GMT

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

Re: Creating a Single Dimension from Multiple fields

ashfaq_haseeb — Thu, 14 Aug 2014 07:37:28 GMT

You can try something like this

Table:

Load EMPID

sum(SickLeave)+sum(CasualLeave)+sum(EarnedLeave) as LeaveBalance

groupby EMPID;

Regards

ASHFAQ

Re: Creating a Single Dimension from Multiple fields

Anonymous — Thu, 14 Aug 2014 07:39:09 GMT

Ya, it is possible.

But i could not get ur requirement.

Could you please explain with an ex?

Re: Creating a Single Dimension from Multiple fields

Anonymous — Thu, 14 Aug 2014 07:41:55 GMT

Do you want to add the three fields as an expression or a dimension?

Re: Creating a Single Dimension from Multiple fields

Thu, 14 Aug 2014 07:44:23 GMT

Hi Balraj,

Where do you want to use this single dimension? Need more clear explanation or a sample qvw or an example.

Please provide.

Re: Creating a Single Dimension from Multiple fields

sibideepak — Thu, 14 Aug 2014 07:45:08 GMT

HiBalraj Ahlawat

You can use cross table while loading data

Take 3 leave as measure & perform sum operation and Leave Balance as new dimension

Cross Table in Qlikview « Learn Qlikview

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

Re: Creating a Single Dimension from Multiple fields

Anonymous — Thu, 14 Aug 2014 07:47:13 GMT

No,

I dont want to add these fields, I want to create a Dimension with these 3 fields and their Value.

Example:

LeaveBalance with three values SickLeave, CasualLeave, EarnedLeave with corresponding values.

Regards,

Balraj

Re: Creating a Single Dimension from Multiple fields

Thu, 14 Aug 2014 07:51:53 GMT

Can you please help me with sample data? So that we can get u a possible solution.

We understand that you want to have a column by name LeaveBalance and it should have the specified three values but where will you have the value for each one stored.

Do you want to achieve this will getting the data in the load script?

Provide with sample data.

Re: Creating a Single Dimension from Multiple fields

Anonymous — Thu, 14 Aug 2014 07:54:25 GMT

Use Value List function to create the Dimension.

Re: Creating a Single Dimension from Multiple fields

MK_QSL — Thu, 14 Aug 2014 08:01:26 GMT

Nothing clear from your question and explanation what exactly you want... Could you please provide little more clarification?

Re: Creating a Single Dimension from Multiple fields

maxgro — Thu, 14 Aug 2014 08:13:48 GMT

see attachment, hope to understand your req

Re: Creating a Single Dimension from Multiple fields

jagan — Thu, 14 Aug 2014 08:29:56 GMT

Hi Balraj,

Try CrossTable() like below

Table:

CrossTable(LeaveType, LeaveCount)

Load EMPID,

SickLeave,

CasualLeave,

EarnedLeave

From DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Creating a Single Dimension from Multiple fields

jagan — Thu, 14 Aug 2014 08:31:36 GMT

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.

Re: Creating a Single Dimension from Multiple fields

rustyfishbones — Thu, 14 Aug 2014 09:02:30 GMT

Did you try using Valuelist as a Calculated Dimension

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

Regards

Re: Creating a Single Dimension from Multiple fields

Anonymous — Thu, 14 Aug 2014 10:58:41 GMT

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

Re: Creating a Single Dimension from Multiple fields

jagan — Thu, 14 Aug 2014 13:06:06 GMT

Hi,

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

Regards,

Jagan.

Re: Creating a Single Dimension from Multiple fields

Anonymous — Mon, 18 Aug 2014 05:10:46 GMT

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