2 Replies Latest reply: Aug 19, 2018 4:46 AM by Arnaldo Sandoval RSS

    Need help in converting Excel formulae

    Devaraj M

      in excel using this formulae, where not sure how to build in Qlik sense;

       

      =IF(AND(A2<>A1,A2<>A3),"Single",IF(A2<>A1,"First",IF(A2<>A3,"Last",IF(A3=A2,"Same",""))))

        

      Name ID List entry
      57136First
      57136Last
      57207First
      57207Same
      57207Last
      57213First
      57213Same
      57213Last
      57214First
      57214Same
      57214Last
      57215First
      57215Same
      57215Last
      57216First
      57216Same
      57216Last
      57219First
      57219Last
      57223First
      57223Same
      57223Last
        • Re: Need help in converting Excel formulae
          Gysbert Wassenaar

          Perhaps like this:

           

          tmpList1:

          LOAD [Name ID] FROM ...excel_file ...

           

          tmpList2:

          LOAD

               [Name ID],

               If(Previous([Name ID] <> [Name ID],'First','Same') AS [List Entry],

               RecNo() as RecNo

          RESIDENT

               tmpList1

          ORDER BY

               [Name ID];


          DROP Table tmpList1;


          List:

          NOCONCATENATE LOAD

               [Name ID],

               If(Previous([Name ID] <> [Name ID],'Last', [List Entry]) AS [List Entry]

          RESIDENT

               tmpList2

          ORDER BY

               [Name ID],

               RecNo desc

               ;


          DROP Table tmpList1;

          • Re: Need help in converting Excel formulae
            Arnaldo Sandoval

            Hi Devaraj,

             

            I am including my solution below, which is based on the "Peek()" function, instead of the "Previous()" suggested by Gisbert, mine only required one "temp" table, which is dropped at the end of the script.

             

            RawData:
            LOAD
                "Name ID"
            FROM [lib://External_Data/Excel_Formula.xlsx]
            (ooxml, embedded labels, table is Data);
            
            
            temp1:
            Load
             RowNo() as RowNumber,
                "Name ID"
            Resident
             RawData
            Order By
             "Name ID";
                
            Drop Table RawData;
            
            
            formula:
            Load
             RowNumber,
                "Name ID",
             if( Peek([Name ID], (RowNumber - 2), 'temp1') <> Peek([Name ID], (RowNumber - 1), 'temp1') and
                Peek([Name ID], (RowNumber - 1), 'temp1') <> Peek([Name ID], RowNumber,       'temp1'), 'Single',
                if( Peek("Name ID", (RowNumber - 2), 'temp1') <> Peek("Name ID", RowNumber - 1, 'temp1'), 'First', 
                if(Peek("Name ID", (RowNumber - 1), 'temp1') = Peek("Name ID", RowNumber, 'temp1'), 'Same', 'Last'))) as Result,
                Peek([Name ID], (RowNumber - 2), 'temp1') as previous,
                Peek([Name ID], (RowNumber - 1), 'temp1') as current,
                Peek([Name ID], RowNumber,       'temp1') as next
            Resident
             temp1;
                
            Drop Table temp1;   
            

             

            Hope this helps,

            Arnaldo