Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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
Highlighted
Creator II
Creator II

Re: Getting the next value if NULL

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

Highlighted
Specialist II
Specialist II

Re: Getting the next value if NULL

Hi Chia,

Try this expression:

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

G.

Highlighted
Partner
Partner

Re: Getting the next value if NULL

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;

Highlighted
Contributor II
Contributor II

Re: Getting the next value if NULL

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.

Highlighted

Re: Getting the next value if NULL

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
Highlighted
Creator II
Creator II

Re: Getting the next value if NULL

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