1 Reply Latest reply: Dec 1, 2015 3:52 PM by Thomas Schmidt RSS

    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?

        • Re: Data Editor combining two IF expressions
          Thomas Schmidt

          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)