4 Replies Latest reply: Jul 12, 2016 5:49 AM by Antonio Mancini RSS

    Get first ID from the data

    Uday Kumar

      Hi,

       

      I have a data like below

       

      ID1ID2
      11
      12
      13
      14
      27
      28
      29
      310
      311
      312

       

      I want the output to look like

       

      ID1ID2
      11
      11
      11
      11
      27
      27
      27
      310
      310
      310

       

      ID1 is having values like 1,2,3 and ID2 is having values like 1,2,3,4,7,8,9,10,11,12

       

      ID1 (ID1 = 1) has mapped to ID2 (1,2,3,4)

      ID1 (ID1 = 2) has mapped to ID2 (7,8,9)

      ID1 (ID1 = 3) has mapped to ID2 (10,11,12)


      I want the values of ID2 1,2,3,4 to have values like 1,1,1,1

      For every combination of ID1, take the first value from ID2 and assign it to other values of ID1.

      How to get that in the script level?


        • Re: Get first ID from the data
          Martin Pohl

          so load like this:

           

          load

          ID1,

          ID2 as ID2temp

          from yoursource;

          left join

          load

          ID1,

          min(ID2temp) as ID2

          Group by ID1;

           

          drop field ID2temp;

          Regards

          • Re: Get first ID from the data
            Liron Baram

            hi

            this script will answer your requirement

            TempData:

            load * inline [

            ID1 ,ID2

            1 ,1

            1, 2

            1 ,3

            1 ,4

            2 ,7

            2 ,8

            2 ,9

            3 ,10

            3 ,11

            3, 12

            ];

             

             

            Data:

            load rowno() As RecordID,

              ID1,

              if(ID1=Previous(ID1),peek('ID3'),ID2) AS ID3

            Resident TempData

            order by ID1,ID2;

             

            drop Table TempData;

            RENAME Field ID3 to ID2;

            • Re: Get first ID from the data
              Mindaugas Bacius

              Also, you can make like this:

              map_Table:
              mapping LOAD ID1
                   ,ID2
              FROM
              [https://community.qlik.com/thread/224657]
              (html, codepage is 1257, embedded labels, table is @1)
              where ID1 <> previous(ID1);
              
              
              Table:
              LOAD ID1
                   ,applymap('map_Table', ID1, ':') as ID2
                   ,rowno()
              FROM
              [https://community.qlik.com/thread/224657]
              (html, codepage is 1257, embedded labels, table is @1);
              

               

              The result and attachment are below:

              Screenshot_1.jpg

              • Re: Get first ID from the data
                Antonio Mancini

                Temp:

                Load ID1,ID2 ..............

                 

                Table:
                NoConcatenate
                LOAD RowNo() as Row//Optional
                  ID1,
                If(ID1=Peek('ID1'),Peek('ID2'),ID2) as ID2
                Resident Temp
                Order By ID1
                ;
                Drop Table Temp;

                 

                Note : If You Want The First Value of ID2 then

                 

                Order By ID1.

                 

                If You Want Min(Value) of ID2 then

                 

                Order By ID1,ID2