topic Re: Creating a New Field in QlikView

Creating a New Field

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a list of Employees who has taken leave. (see file attached). How do I create a field (in the backend preferably) that indicates "Yes' for Employees who has taken at least once "Taken more than 10 days" leave and 'No' for the alternative

So, in the screenshot below, for Employee 11, he has "Taken more than 10 days" , ie 18 days. So this employee therefore should have 'Yes' to his name. Whereas Employee 100, 'No' is assigned to his name.

Kind regards

Nayan

Re: Creating a New Field

Anil_Babu_Samineni — Wed, 06 Dec 2017 08:53:52 GMT

May be this?

If([Leave Days] > 10, 'Yes', 'No') as [Taken More Than 10 Days]

Re: Creating a New Field

Anonymous — Wed, 06 Dec 2017 08:58:41 GMT

Hi Anil

Thank you for your reply. However, as mentioned, for eg, Employee 11, I want a field, so that 'Yes' to appear for every transaction line for this Employee.

The formula that you have give, already have that.

Kind regards

Nayan

Re: Creating a New Field

Anil_Babu_Samineni — Wed, 06 Dec 2017 09:01:13 GMT

that means, Employees 10, 101, 102, 11 should be Yes and 1, 100, 12 should be no, Is this your question?

Re: Creating a New Field

Anonymous — Wed, 06 Dec 2017 09:02:27 GMT

Yes, that is correct.

Re: Creating a New Field

devarasu07 — Wed, 06 Dec 2017 09:04:17 GMT

Hi,

Try to use mapping load and apply map fxn

mapEMP:
mapping load 
[Employee Number],
 if([Leave Days]>10,'Yes','No') as [Taken More than 10 Days]
 FROM

(ooxml, embedded labels, table is Sheet1);

Fact:
load *, ApplyMap('mapEMP', [Employee Number],'NA') as  [Taken More than 10 Days];
LOAD [Employee Number], 
    [Employee Name], 
    LeaveStartDate, 
    LeaveEndDate, 
    [Employees within 3 Months Cycle], 
    [Leave Days], 
    [Days Taken]    
FROM

(ooxml, embedded labels, table is Sheet1);

Re: Creating a New Field

gys1992infosys — Wed, 06 Dec 2017 09:04:35 GMT

Hi Nayan,

For example: If ([Leave Days]>10,'Yes','No'), this expression can also apply in colour:

if([Leave Days]>10,red(),yellow())

Re: Creating a New Field

Anil_Babu_Samineni — Wed, 06 Dec 2017 09:05:02 GMT

May be this?

If([Employee Name] = Previous(Employee Name] and [Leave Days] > 10, 'Yes', 'No') as [Taken More Than 10 Days]

Re: Creating a New Field

avinashelite — Wed, 06 Dec 2017 09:07:42 GMT

As per my understanding you need Yes or No based on the Complete year .....

if the is the case try like this

LOAD

if(Total_Leave_Days>18,'Yes','No') as Total_Leave_Days,

[Employee Number];

LOAD

SUM([Leave Days]) as Total_Leave_Days,

[Employee Number]

resident

main_table

group by

[Employee Number];

NOTE: if you need year wise you could inculde year also

Re: Creating a New Field

mathieupignon — Wed, 06 Dec 2017 09:08:56 GMT

Hi,

You can manage this in the Data Load Editor, with this Load Script:

[Sheet1]:
LOAD
    "Employee Number",
    "Employee Name",
    LeaveStartDate,
    LeaveEndDate,
    "Employees within 3 Months Cycle",
    "Leave Days",
    "Days Taken" 
FROM [lib://Desktop/Leave Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Results]:
LOAD
    "Employee Name",
    MAX("Leave Days") as "Max Leave Days",
    IF(MAX("Leave Days")>10,'Yes','No') as "Taken More than 10 Days"
RESIDENT [Sheet1]
GROUP BY "Employee Name";

That results in

Re: Creating a New Field

Anonymous — Wed, 06 Dec 2017 09:11:09 GMT

Thank you for your reply Devarasu. Will try it out and let you know.

Kind regards

Nayan

Re: Creating a New Field

Anonymous — Wed, 06 Dec 2017 09:12:10 GMT

Hi Avinash

Yes, based no a complete year.

Kind regards

Nayan

Re: Creating a New Field

Anonymous — Wed, 06 Dec 2017 09:12:56 GMT

Hi Gary

Thank you for your reply. However I already have this field.

Kind regards

Nayan