topic Re: Create new calculated column (If, Else statement) in QlikView

Create new calculated column (If, Else statement)

Wed, 25 Nov 2020 16:16:04 GMT

Hello community,

I'm new to QlikView and I'm hoping someone can point me in the right direction. I'm working with your typical HR data (EmpID, First Name, Last Name, etc) and I'm in need to start creating a dashboard where I can monitor gaps in my data, such as when an Active Employee has an Invalid or Null value for Manager ID, Job Grade and so on.

I understand that there are different ways to accomplish this through the script load as well as through a conditional variable, so I'm confused as to how to go about achieving this in the most efficient way. Perhaps by creating a new column in my dataset through a calculated If, Else statement?

Thank you in advance for any assistance you can provide!

-Andrew

Re: Create new calculated column (If, Else statement)

vishsaggi — Wed, 15 Mar 2017 21:55:58 GMT

Can you explain a little what exactly you want to create and use this new column in?

You can create a flag in your load script like:

LOAD ...,

IF(Len(Trim(ManagerID)) = 0 AND Len(Trim(JobGrade)) = 0, 0, 1) AS NullFlag

FROM yourdatasource;

Re: Create new calculated column (If, Else statement)

Anonymous — Wed, 15 Mar 2017 23:43:29 GMT

I hope i've understood your question, you can use a condition in the data load, indicating that if some field is empty, a new column specify that the data is incomplete;

Something like this

if( len(EmpID)=0 or len([First Name])=0 or len(Last Name)=0 ,'Incomplete','Complete') as Detail

len count the number of characters, if the data is empty then count 0

Re: Create new calculated column (If, Else statement)

ahaahaaha — Thu, 16 Mar 2017 07:47:40 GMT

Hi Andrew,

Alternatively as variant, you can simply sort the field you need in the properties of the chart or list and all the empty values will be either at the beginning or at the end of the list, depending on the type of sorting.

Regards,

Andrey

Re: Create new calculated column (If, Else statement)

Tue, 21 Mar 2017 18:06:40 GMT

Thanks Vishwarath, my intention is to be able to create a chart where I can highlight all employee records without an assigned Level 1 Manager (same as direct supervisor) by Country and Region so I can easily point to locations where missing manager is a recurring problem. In your opinion, what would be the best way to approach this? Through a new 'AS' column in the load script?

Thanks so much again for your assistance!