topic Re: How insert missing records into qlikview table in QlikView

How insert missing records into qlikview table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am kalyan new to the qlikview .Please guide me

I have following requirement to insert records into existing qlikview table with null values.

Ex: For each student having 3.exams they should attend but some students are absence due to the various reasons

The source file captured only attended exam details of students.Hence if student absent any exam need to insert null records with student ID.

i.e each student ID should present with 3 records whether he is attended or not

Data in source file:(only 1 file)

Student ID ExamID Marks Status

1 Sub1 50 Present

1 Sub2 80 Present

1 Sub3 90 Present

2 Sub1 60 Present

2 Sub2 60 Present

3 Sub1 30 Present

Expected Result:(How to insert the records marked in red)

Student ID ExamID Marks Status

1 Sub1 50 Present

1 Sub2 80 Present

1 Sub3 90 Present

2 Sub1 60 Present

2 Sub2 60 Present

2 Sub3 Null Absent

3 Sub1 30 Present

3 Sub2 Null Absent

3 Sub3 Null Absent

Re: How insert missing records into qlikview table

suepenick — Mon, 29 Jan 2018 15:51:38 GMT

in your chart expression, if(isnull(Marks), 0, Marks)

this will make the expression work like this:

if the field Marks is Null, then replace the null with a zero

if the field is not null, then represent what is in the field Marks.

Note: When you start having files with multiple left joins (to create one file) in the Load Script,

I have found that after I have the file complete with all joins, I reload the file In Resident (no concatenate)...and do the if isnull for the fields where null is possible and replace the nulls with zeros or ' ' blanks or text 'blank';

drop the original table;

then when you are manipulating, summing, counting, you will have a very clean table to work with.

too many nulls will cause data to be misrepresented when you are pivoting, if the data is a primary pivot...

i.e. Products missing codes, and you sort and subtotal sales by those codes.

Re: How insert missing records into qlikview table

Anonymous — Mon, 29 Jan 2018 18:04:56 GMT

Thank you panick.....but I want to display 3 records for each student id in pivot table ....If I add expression as u suggested will insert a new record with null as marks in pivot table?