4 Replies Latest reply: Apr 19, 2012 4:56 AM by mlaarhoven RSS

    IF statement

      I have a problem with building an IF statement.

       

      the data is as follows:

       

      ID      Indication          Date               (NEW COLOM)

      61     Retrieved          01-01-2012     NOT

      61     Retrieved          01-02-2012     NOT

      61     Not Retrieved     01-03-2012     NOT

      61     Not Retrieved     01-04-2012     NOT

       

      So for a certain ID field i have two possible status depending on the time. I want to make a new field that says in every situation that there is data not retrieved.

       

      IF (Indication = 'Not Retrieved', 'NOT', 'YES') as [NEW COLOM],

      This will only give met Not where it says not retrieved. How do i get this for the retrieved records as well.

       

      goal is to know which ID fields still have some missing values. I want to make this in the QlikView script.

       

      Can anyone help me with this issue?

        • IF statement
          Celambarasan Adhimulam

          Hi,

               Check with this

               IF (Indication = 'Not Retrieved', 'NOT',IF (Indication = 'Retrieved', 'Retrieved','Missing')) as [NEW COLOM],

           

          Celambarasan

            • IF statement

              Hi Celambrasan,

               

              Thank you for the reply, your answer almost covers it. However it gives the status 'retrieved' in the NEW COLOM where the indication is retrieved.

               

              It should give me 'NOT' in the new colom because for that ID field there is an indication 'Not retrieved' anywhere in time. Example:

               

              ID      Indication          Date               (NEW COLOM)

              61     Retrieved          01-01-2012     NOT

              61     Retrieved          01-02-2012     NOT

              61     Not Retrieved     01-03-2012     NOT

              61     Not Retrieved     01-04-2012     NOT

               

              ID      Indication      Date                (NEW COLOM)

              51     Retrieved    01-01-2012         YES

              51     Retrieved    01-02-2012         YES

              51     Retrieved     01-03-2012        YES

              If for every date the indication is retrieved then it should give yes for the ID field.

               

              Thanks in advance,

                • IF statement
                  Celambarasan Adhimulam

                  Hi,

                       Then try with this

                        IF (Indication = 'Not Retrieved', 'NOT',IF (Indication = 'Retrieved', 'YES','Missing')) as [NEW COLOM],

                  OR

                       Pick(Match(Indication,'Not Retrieved','Retrieved'),'NOT','YES')

                  Celambarasan

                    • IF statement

                      Hi Celambrasan,

                       

                      thank you again for your comment. I evently fixed it (with some help )with the following script:

                      GroupingTabel:

                      Load ID,

                                      If(Only(Indication)=Retrieved,'No','Yes') AS Newkolom

                      Resident data

                      Group by ID;

                       

                      Left join (data)

                      Load *

                      Resident GroupingTabel;

                      Drop GroupingTabel;

                       

                      kind regards,