Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Data Editor combining two IF expressions

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?

1 Reply
t_schmidt
New Contributor II

Re: Data Editor combining two IF expressions

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:

NamedeptEintritt/ EinsatzstartsEinsatzende / tats. Austritt (Feld in DB)EintritteAustritte
Smithfinance12.02.201530.06.2015EintritteAustritt
Doeproduction24.09.201423.09.2015Eintritt vor 2015Austritt
Krugerhealthcare24.06.201523.06.2016EintritteAustritt nicht in 2015
Myersoperations30.09.201529.03.2016EintritteAustritt nicht in 2015

new (after crosstable transformation):

NamedeptEintritt/ EinsatzstartsEinsatzende / tats. Austritt (Feld in DB)Ein-/Austrittedetail
Smithfinance12.02.201530.06.2015EintritteEintritte
Smithfinance12.02.201530.06.2015AustritteAustritt
Doeproduction24.09.201423.09.2016EintritteEintritt vor 2015
Doeproduction24.09.201423.09.2016AustritteAustritt
Krugerhealthcare24.06.201523.06.2016EintritteEintritte
Krugerhealthcare24.06.201523.06.2016AustritteAustritt nicht in 2015
Myersoperations30.09.201529.03.2016EintritteEintritte
Myersoperations30.09.201529.03.2016AustritteAustritt 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)