topic Re: Creating a new dimension in Qlik Sense in App Development

Creating a new dimension in Qlik Sense

Fri, 27 Nov 2015 12:18:46 GMT

Hi,

im facing following problem right now:

I got a date_field in may database which marks the date a person left the company.

For example:

Person	End of duty (Date
1	22.01.2015
2	23.12.2015
3	31.07.2015

Now i want to retrieve a new data_field which shows how many person already left the company and how many are left.

For example today (27.11.2015) the persons '1' and '3' already left but '2' is still on a running contract.

Id like to create a new field giving me the information whether the contract is still running or not.

Person	End of duty (Date)	Status
1	22.01.2015	left
2	23.12.2015	running contract
3	31.07.2015	left

Anyone having an idea how I can achieve this second table? And maybe some hints how to create the expression for it?

Thanks a lot!

Re: Creating a new dimension in Qlik Sense

sunny_talwar — Fri, 27 Nov 2015 12:40:46 GMT

From what you have provided how did you know that 1 and 3 left, but 2 is running contract? I don't really understand the logic here?

Re: Creating a new dimension in Qlik Sense

Fri, 27 Nov 2015 12:48:36 GMT

Oh ok my provided information might be a bit confusing.

The given date in the table marks the point when a person left the company. So today (27.11.2015) person 1 and 3 should be marked as 'left', because their contract ended on the 22.01.2015 and 31.07.2015. Person 2 will be with the company until 31.12.2015, so from today point of view he has still a 'running contract'.

So im looking to add the attribute 'left' & 'running'.

If [End of Duty(Date)] < 'Today' AND [End of Duty(Date)] = 'empty/null'

then 'left'

else 'running'

Re: Creating a new dimension in Qlik Sense

sunny_talwar — Fri, 27 Nov 2015 12:54:56 GMT

Got it

Try this:

SET DateFormat='DD.MM.YYYY';

Table:

LOAD Person,

[End of duty (Date)]

If([End of duty (Date)] < Today(), 'Left', 'Running Contract') as Status

FROM source

Re: Creating a new dimension in Qlik Sense

Fri, 27 Nov 2015 13:22:43 GMT

Thanks a lot it worked for me!

Unfortunately I found some NULL in my dataset. In this case NULL means also 'left', how can I add this?

Re: Creating a new dimension in Qlik Sense

sunny_talwar — Fri, 27 Nov 2015 14:04:58 GMT

Try this:

If([End of duty (Date)] < Today() or Len(Trim([End of duty (Date)])) = 0, 'Left', 'Running Contract') as Status

Re: Creating a new dimension in Qlik Sense

Fri, 27 Nov 2015 14:52:10 GMT

Thanks a lot again for the quick help! All worked out so far, instead of 0 I used ''.

One last question for the moment:

Is there any possible way to not take the "else" statement 'Running Contract' into account? So the Status will only hold the if value 'left'.

Regards!

Re: Creating a new dimension in Qlik Sense

sunny_talwar — Fri, 27 Nov 2015 15:29:50 GMT

Try this:

If([End of duty (Date)] >= Today(), 'Running Contract', 'Left') as Status