2 Replies Latest reply: Apr 14, 2016 12:09 PM by jagan mohan rao appala RSS

    countif in load script

    Paul Wonford

      hi guys. I have the following load script

       

      Load

       

       

           title as Title,

           [forename 1] as Forename1,

           [Employee] as Employee number

       

      From [c:\test.xlsx (ooxml, embedded labels, Table is 'Test');

       

      This will import individual information.

       

      I also need to run a check which looks down the entire table and identifies employee numbers which appear more than once but when I do this as above it shows

       

      TitleForename1employee numbercount of employee number
      MrBob12341
      MrSid12341
      MrJohn12341
      MrEd44441

       

      What i want it to show is

       

      TitleForename1employee numbercount of employee number
      MrBob12343
      MSid12343
      MrJohn12343
      MrEd44441

       

      I think you could do this with some kind of countif in the load script but i've searched and i cannot seem to get it. Any suggestions?

        • Re: countif in load script
          Sunny Talwar

          Try this:

           

          Table:

          LOAD

               title as Title,

               [forename 1] as Forename1,

               [Employee] as [Employee number]

          From [c:\test.xlsx (ooxml, embedded labels, Table is 'Test');

           

          Left Join (Table)

          LOAD [Employee number],

                    Count([Employee number]) as Count

          Resident Table

          Group By [Employee number];

          • Re: countif in load script
            jagan mohan rao appala

            Hi,

             

            You can also try like this

             

            Data:

            Load

                 title as Title,

                 [forename 1] as Forename1,

                 [Employee] as Employee number,

            If(Employee = Peek(Employee),  RangeSum(Peek(Emp_Count), 1), 1) AS Emp_Count

            From [c:\test.xlsx (ooxml, embedded labels, Table is 'Test')

            ORDER BY Employee;

             

            Hope this helps you.

             

            Regards,

            Jagan.