1 Reply Latest reply: Sep 29, 2011 11:59 PM by Sridhar Ethiraj RSS

    How to Concise Duplicate Value

    Jeffrey Chan Sep 29, 2011 11:30 PM

      Hi,

       

      I got a table with same TicketNumber value but inside contain different value for each TicketNumber such as Station and Amount as following :

       

      TicketNumber     Station     Amount    

      1                         E1               0

      1                         K4               3

      1                         A2               3

      2                         E2               0

      ....

       

       

      How can i going to concise TicketNumber into single record? as following

       

      Ticket Number     EnterStation     ExitStation     Amount

      1                              E1                    A2               3

      • 509 Views
      • Tags:
        • Re: How to Concise Duplicate Value
          Sridhar Ethiraj
          Sridhar Ethiraj Sep 29, 2011 11:59 PM (in response to Jeffrey Chan)

          Jeffrey,

           

          You can do this using FirsValue() and LastValue() Function in QlikVeiw.

           

          Your Script should be some thing like below one.

           

          Test:

          Load * inline [

          TicketNumber, Station, Amount   

          1, E1, 0

          1, K4, 3

          1, A2, 3

          2, E2, 0

          2, K5, 6

          2, A2, 6

          ];

           

          Inner Join

           

          Load TicketNumber,

          FirstValue(Station) as EntryStation,

          LastValue(Station) as Station,

          LastValue(Station) as ExitStation

          Resident Test Group By TicketNumber;

           

          Have a look at the attached application.

           

          Hope this helps you.

           

          -Sridhar