How to Check For Missing Data

[PurplePills555]

Hi All,

I have a scenario. I have two tables. A form table and a form answers table that are linked via an ID.

Ie

Form ID    Form Name Date of Form

555              wlejr            15/10/18

and 

Form ID  Question Mapping ID  Question    Answer

555                          124214                Do you like...     Yes

555                           13456                 Do you need...  No

To save server resources we only bring in those answers for forms where they are completed. However I want to identify forms that have missing answers to questions for data quality. There are over 100 questions in the form and I want to identify which Questions have missing answers.

Can anyone suggest how to get around this? I was thinking using a master mapping list of the IDs but I can't do apply maps on 100 questions to see if there is an answer and I don't want to have to name fields manually by hand.

I need something that looks into all the Question Mapping IDs and identifies whether that form ID has an answer and spits out which questions does not have answers.

I hope that makes sense and I can provide more details if you need!

Thanks 

I need to know which forms have which answers missing

[asinha1991]

need more clarity on what you mean by missing answers,

there will be entry in formanswer table for question but answer will be blank?

or there will no entry for that form in formanswer table?

is there a separate table with list of questions and you want to see if all questions have entry in answer table for given form?

[PurplePills555]

is there a separate table with list of questions and you want to see if all questions have entry in answer table for given form?

Yes this.

[asinha1991 · Accepted solution]

create cross product of form and question. something like this

Crossproduct:

Load formId, 1 as joinkey Resident Form;

outer join

Load Question, 1 as joinkey Resident QuestionList;

simply outer join this table(crossproduct)  with form answer based on question id and formid.

then on top the newly created table, add a condition if(isnull(answer), 'Missing', answer) as answer

drop old table.

in front end you can find the form with missing question using simple set analysis

formid as dimension

if (count({<answer={'Missing'}>} answer)>1,'with missing answer','without missing answer')

[martinpohl]

try this

NULLASVALUE *;

Set NullValue = ' ';

Users:
LOAD * INLINE [
FormID, FormName
555, Name1
556, Name2
];

Answers:
LOAD * INLINE [
FormID, QuestionID, Answer
555, 12345, Yes
555, 345, No
556, 334, Yes
556, 12345, No
];

ListofQuestions:
load distinct
QuestionID
resident Answers;

let vQuestions = NoOfRows('ListofQuestions')-1;

for vQuestion = 0 to $(vQuestions)
let QID = peek ('QuestionID',$(vQuestion),'ListofQuestions');

left join (Users) load
FormID,
Answer as '$(QID)'
resident Answers
where QuestionID = $(QID)
;
next

drop table Answers;

List:
NoConcatenate load * resident Users;
drop table Users;

then you have each question as a field, each answer as fieldtext and blanks for not answered questions / each FormID

Regards