6 Replies Latest reply: Nov 5, 2018 6:05 AM by Vitalii Chuprina RSS

    Getting the next value if NULL

    Chia Jia Xiang

      Hi all,

       

      Table 1: 

      EMPLIDSTRMGPA
      115200.000
      115303.750
      116103.940
      116203.940
      116304.040
      117104.040
      118104.090
      118204.090
      11830NULL

       

       

      This will be done in the loading script. What i want to be able to do is to get the GPA from MAX(STRM)  if it is not null.

       

      Let me elaborate. If there is a value in 1830 , then pick up the GPA from there.

      In this scenario, where the GPA in the highest STRM is null, i want to be able to get the GPA of 4.090 from STRM 1820.

       

      Any form of help is greatly appreciated.

       

      Thank you for your time!

        • Re: Getting the next value if NULL
          Vitalii Chuprina

          Hi Chia,

           

          You can use following:

           

          Table1:

          Load * Inline [
               EMPLID,STRM,GPA
               1,1520,0.000
               1,1530,3.750
               1,1610,3.940
               1,1620,3.940
               1,1630,4.040
               1,1710,4.040
               1,1810,4.090
               1,1820,4.090
               1,1830,NULL
          ];


          Table2:
          Load
                max(GPA) AS max,
                EMPLID
          Resident Table1
          Where not isnull(GPA) and GPA <> 'NULL'
          Group BY EMPLID;

           

          Thanks,

          Vitalii

            • Re: Getting the next value if NULL
              Chia Jia Xiang

              Hi, in this case, it might work because the latest term has to highest CGPA, however it is not always the case that the CGPA will increase over the terms(STRM).

              For example, the last STRM could have a GPA of 3.2. And i would like to pick that value out.

                • Re: Getting the next value if NULL
                  Jonathan Dienst

                  To fill in a null with the previous value:

                   

                  LOAD EMPLID, STRM, Alt(GPA, Previous(GPA)) as GPA

                  FROM ....

                   

                  To fill in multiple nulls from the last non-null:

                   

                  LOAD EMPLID, STRM, Alt(GPA, Peek(GPAFinal)) as GPAFinal

                  FROM ....

                  • Re: Getting the next value if NULL
                    Vitalii Chuprina

                    Hi Chia,

                     

                    In this solution I use two mapping table. In first table Qlik finds Max (STRM) per EMPLID where GPA <> Null and second map is just a mapping with GPA for STRM.

                     

                    Table1:
                    Load * Inline [
                    EMPLID,STRM,GPA
                         1,1520,0.000
                         1,1530,3.750
                         1,1610,3.940
                         1,1620,3.940
                         1,1630,4.040
                         1,1710,4.040
                         1,1810,4.090
                         1,1820,4.090
                         1,1830,NULL
                         1,1840,3.2
                         1,1850,NULL
                         2,1530,3.750
                         2,1610,3.940
                         2,1620,NULL
                         2,1930,3.95
                         3,1940,2.11
                         3,1950,NULL
                    ];


                    MaxSTRM_Map: 
                    Mapping LOAD EMPLID,
                      max(STRM)
                    Resident Table1
                    Where not isnull(GPA) and GPA <> 'NULL'
                    Group BY EMPLID;

                     

                    GPA_Map: 
                    Mapping Load
                       STRM,
                       GPA
                    Resident Table1;

                     

                    Table12:
                    NoConcatenate
                    Load
                          EMPLID,
                          STRM,
                          GPA,
                    ApplyMap('GPA_Map',ApplyMap('MaxSTRM_Map', EMPLID, 'N/A')) AS MaxGPA
                    Resident Table1;

                    DROP Table Table1;

                     

                    Thanks,

                    Vitalii

                • Re: Getting the next value if NULL
                  Gabor Tarnoczai

                  Hi Chia,

                   

                  Try this expression:

                   

                  =FirstSortedValue(GPA,-(STRM&GPA))

                   

                  G.

                  • Re: Getting the next value if NULL
                    Niclas Anderström

                    Hi,

                     

                    Since I assume you want to do this for every EMPLID individually I would create a mapping table:

                     

                    Temp:

                    LOAD

                        EMPLID,

                        Max(GPA) as MaxGPA

                    Resident source

                    group by EMPLID;

                     

                    mGPA:

                    Mapping LOAD

                        *

                    Resident Temp;

                     

                    Drop table Temp:

                     

                    Table:

                    noconcatenate

                    LOAD

                        EMPLID,

                        STRM,

                        if(Isnull(GPA), ApplyMap('mGPA',EMPLID),GPA) as GPA

                    Resident source;


                    Drop table source;