Getting the next value if NULL

[Anonymous]

Hi all,

Table 1: 

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

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!

[vitaliichupryna]

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]

Hi Chia,

Try this expression:

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

G.

[niclaz79]

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]

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]

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

[vitaliichupryna]

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