How insert missing records into qlikview table

[Anonymous]

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

[mdmukramali]

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;

[mdmukramali]

PFA.

[Anonymous]

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

Kindly resend

[mdmukramali]

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;

[Anonymous]

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

[shiveshsingh]

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