Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Getting the next value if NULL

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!

6 Replies
vitaliichupryna
Creator III
Creator III

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

undergrinder
Specialist II
Specialist II

Hi Chia,

Try this expression:

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

G.

niclaz79
Partner - Creator III
Partner - Creator III

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;

Anonymous
Not applicable
Author

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.

jonathandienst
Partner - Champion III
Partner - Champion III

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 ....

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
vitaliichupryna
Creator III
Creator III

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