Discussion board where members can get started with Qlik Sense.
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!
You can use following:
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, EMPLIDResident Table1Where not isnull(GPA) and GPA <> 'NULL' Group BY EMPLID;
Try this expression:
Since I assume you want to do this for every EMPLID individually I would create a mapping table:
Max(GPA) as MaxGPA
group by EMPLID;
Drop table Temp:
if(Isnull(GPA), ApplyMap('mGPA',EMPLID),GPA) as GPA
Drop table source;
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.
To fill in a null with the previous value:
LOAD EMPLID, STRM, Alt(GPA, Previous(GPA)) as GPA
To fill in multiple nulls from the last non-null:
LOAD EMPLID, STRM, Alt(GPA, Peek(GPAFinal)) as GPAFinal
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 Table1Where not isnull(GPA) and GPA <> 'NULL' Group BY EMPLID;
GPA_Map: Mapping Load STRM, GPAResident Table1;
Table12:NoConcatenateLoad EMPLID, STRM, GPA, ApplyMap('GPA_Map',ApplyMap('MaxSTRM_Map', EMPLID, 'N/A')) AS MaxGPAResident Table1;
DROP Table Table1;