Data Editor combining two IF expressions

[#1]

Hi,

right now I try to combine two IF expressions, but i don't really know how.

I got these two statements:

IF ([Eintritt/ Einsatzstarts] > '01.01.2015' AND [Eintritt/ Einsatzstarts] < Today(), 'Eintritte', 'Eintritt vor 2015') as "Eintritte", IF ([Einsatzende / tats. Austritt (Feld in DB)] > '01.01.2015' AND [Einsatzende / tats. Austritt (Feld in DB)] < Today(), 'Austritt', 'Austritt nicht in 2015') as "Austritte",

My plan is to combine these two and get a new column where only "Eintritte" and "Austritt" are in. So both 'as' reference on the same column and only "Eintritte" and "Austritt" will be written into.

Any ideas how to achieve this?

[Anonymous]

Hi Philipp,

take a look at the crosstable function for the load script. With 'crosstable' you rotate the existing two columns and create two new ones in one step. (it is nearly similar to the special-paste option "transpose" in MS Excel)

Example:

yourTable:

crosstable ([Ein-/Austritte], [detail], 4)

Load

Name,

dept,

[Eintritt/ Einsatzstarts],

[Einsatzende / tats. Austritt (Feld in DB)],

IF ([Eintritt/ Einsatzstarts] > '01.01.2015' AND [Eintritt/ Einsatzstarts] < Today(), 'Eintritte', 'Eintritt vor 2015') as "Eintritte",

IF ([Einsatzende / tats. Austritt (Feld in DB)] > '01.01.2015' AND [Einsatzende / tats. Austritt (Feld in DB)] < Today(), 'Austritt', 'Austritt nicht in 2015') as "Austritte"

From *

[Ein-/Austritte] = this is the new column where "Eintritte" and "Austritte" where collected

[detail] = this is the new column where the former values of "Eintrtte" and "Austritte" are collected

4 = this is the number of columns of your existing table which you want to keep as they are and shouldn't be transformed into crosstable. In the example above you skip "Name", "Agency", [Eintritt/ Einsatzstarts] and [Einsatzende / tats. Austritt (Feld in DB)], all columns after these numbers of excluded column would be transformend into a crosstable.

If I look at your IF-statements, then you have two values for each field - is this correct? Means "Eintritte" can be the value 'Eintritte' or 'Eintritt vor 2015'. "Austritte" can be the value 'Austritt' or 'Austritt nicht in 2015' - correct?

I try to visualize it via 'actually' table (please correct my suggestions, this is what I interpreted with your given formulas^^) and 'new' (cross-)table

actually:

Name	dept	Eintritt/ Einsatzstarts	Einsatzende / tats. Austritt (Feld in DB)	Eintritte	Austritte
Smith	finance	12.02.2015	30.06.2015	Eintritte	Austritt
Doe	production	24.09.2014	23.09.2015	Eintritt vor 2015	Austritt
Kruger	healthcare	24.06.2015	23.06.2016	Eintritte	Austritt nicht in 2015
Myers	operations	30.09.2015	29.03.2016	Eintritte	Austritt nicht in 2015

new (after crosstable transformation):

Name	dept	Eintritt/ Einsatzstarts	Einsatzende / tats. Austritt (Feld in DB)	Ein-/Austritte	detail
Smith	finance	12.02.2015	30.06.2015	Eintritte	Eintritte
Smith	finance	12.02.2015	30.06.2015	Austritte	Austritt
Doe	production	24.09.2014	23.09.2016	Eintritte	Eintritt vor 2015
Doe	production	24.09.2014	23.09.2016	Austritte	Austritt
Kruger	healthcare	24.06.2015	23.06.2016	Eintritte	Eintritte
Kruger	healthcare	24.06.2015	23.06.2016	Austritte	Austritt nicht in 2015
Myers	operations	30.09.2015	29.03.2016	Eintritte	Eintritte
Myers	operations	30.09.2015	29.03.2016	Austritte	Austritt nicht in 2015

With this solution your datasets will be duplicated because the new field [Ein-/Austritte] gets 2 new values (Eintritte, Austritte).

I have to admit that I have no idea if this is what you want to have - maybe an example file and a little description or what you want to do with this new field could clean up the suggestion to a suggestion to a helpful support 

Kind regards

T.

(let me know if you are a german speaking guy, I am such a guy too - could be easier to communicate)