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

mdmukramali — Mon, 29 Jan 2018 18:01:04 GMT

Hi ,

Can you try something like this.

Table1:
LOAD *,
[Student ID]&'-'&ExamID as KEY
 Inline
[
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
];


LOAD Distinct 
[Student ID],
'Sub1' as ExamID,
[Student ID]&'-'&'Sub1' as KEY,
Null() as Marks,
'Absent' as Status
Resident Table1
Where NOT Exists(KEY,[Student ID]&'-'&'Sub1');


LOAD Distinct
[Student ID],
'Sub2' as ExamID,
[Student ID]&'-'&'Sub2' as KEY,
Null() as Marks,
'Absent' as Status
Resident Table1
Where NOT Exists(KEY,[Student ID]&'-'&'Sub2');


LOAD Distinct
[Student ID],
'Sub3' as ExamID,
[Student ID]&'-'&'Sub3' as KEY,
Null() as Marks,
'Absent' as Status
Resident Table1
Where NOT Exists(KEY,[Student ID]&'-'&'Sub3');


DROP Field KEY From Table1;

Re: How insert missing records into qlikview table

mdmukramali — Mon, 29 Jan 2018 18:02:26 GMT

PFA.

Re: How insert missing records into qlikview table

Anonymous — Mon, 29 Jan 2018 18:08:39 GMT

Thank you .....Text is not visible in your message

Kindly resend

Re: How insert missing records into qlikview table

mdmukramali — Mon, 29 Jan 2018 18:16:47 GMT

Hi,

Table1:

LOAD *,

[Student ID]&'-'&ExamID as KEY

Inline

[

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

];

LOAD Distinct

[Student ID],

'Sub1' as ExamID,

[Student ID]&'-'&'Sub1' as KEY,

Null() as Marks,

'Absent' as Status

Resident Table1

Where NOT Exists(KEY,[Student ID]&'-'&'Sub1');

LOAD Distinct

[Student ID],

'Sub2' as ExamID,

[Student ID]&'-'&'Sub2' as KEY,

Null() as Marks,

'Absent' as Status

Resident Table1

Where NOT Exists(KEY,[Student ID]&'-'&'Sub2');

LOAD Distinct

[Student ID],

'Sub3' as ExamID,

[Student ID]&'-'&'Sub3' as KEY,

Null() as Marks,

'Absent' as Status

Resident Table1

Where NOT Exists(KEY,[Student ID]&'-'&'Sub3');

DROP Field KEY From Table1;

Re: How insert missing records into qlikview table

Anonymous — Mon, 29 Jan 2018 19:09:34 GMT

Thank you mukram

The only doubt is he KEY column formed in each resident load and again same string used in not exist .....please elaborate

Re: How insert missing records into qlikview table

shiveshsingh — Tue, 30 Jan 2018 11:22:50 GMT

Key in resident load is compared with the Key in parent table. Record will not be loaded if string in resident load matches parent table Key field