10 Replies Latest reply: Jun 29, 2017 5:05 AM by Stefan Wühl RSS

    IF(SUM())

    Salamon Musayev

      Hello,

       

       

      I have a field called visitCount.  Using this field, I calculate sum of visit that client had ie  sum(visitCount).

       

      I would like to create a flag, that would allow me to select client that had more that 6 visits.

       

      I have been trying to  --         if(sum(visitCount) > 6, 'y', 'n') but it has not worked.

       

      Can anyone suggest an alternative method of tackling this issue.

       

       

      Thank you,

        • Re: IF(SUM())
          Sunny Talwar

          Are you doing this in the script? Make sure you use Group by clause if you are doing this in the script

            • Re: IF(SUM())
              Salamon Musayev

              not in the data load editor

                • Re: IF(SUM())
                  Sunny Talwar

                  Then where?

                    • Re: IF(SUM())
                      Salamon Musayev

                      Each record under the visitCount has  a value of 1 for a visit.


                      I created a table with the following field


                      id| ClientName|  Start Year| Start Month| #ofVisits [which is  sum(visitCount)], Morethan6VisitFlag

                      1      AA               2017           2                  10                                                       y

                      2      AB               2017           3                   4                                                        n

                      3     AC               2017           3                    2                                                        n

                       

                      Separately I would like to create a flag when there are more than 6 visits.

                       

                      Hope this helps,

                       

                      if there is another method to do it, then I would be glad to learn.

                       

                      Thank you

                • Re: IF(SUM())
                  kevin vargas

                  Hi Salamon Musayev, in this case if u mention that the fiel visitcount save number 1 for each visit i think the correct form for u solution would be this.

                   

                  1. Do the load of Your table, for the fuield countvisit must be a count(), in order to count all the visits for each Clientname, for this the group by, and in the superior part of the table a precedent load giving it the codition for the flag.

                   

                   

                  TABLE_TEMP:

                  Load

                  *,

                  if(VISITCOUNTER>6,'Y', 'N') AS FLAG;

                  LOAD

                      id,

                      ClientName,

                      "  Start Year",

                      "Start Month",

                      COUNT(visitcount) as VISITCOUNTER

                  FROM [table_source]

                  GROUP BY id, ClientName, "  Start Year", "Start Month";

                   

                  I could undertand this, if I am wrong please let me know.