Discussion Board for collaboration on QlikView Scripting.
I have the following 2 tables (from a 3rd party source):
Question Answer Choice
Job Function District Nurse 01
Job Function Practice Nurse 02
Job Function Specialist Nurse 03
Job Function Clinical Nurse 04
Job Function Health Visitor 05
Specialist Areas Oncology 01
Specialist Areas Diabetes 02
Specialist Areas Wound Care 03
Specialist Areas A & E 04
Specialist Areas Travel Health 05
Industry Sector Primary Care 01
Industry Sector Secondary Care 02
Industry Sector Private Sector 03
CustomerID Question Choice
1 Job Function 01
1 Specialist Areas 02
1 Industry Sector 01
2 Job Function 05
2 Specialist Areas 03
3 Job Function 02
3 Specialist Areas 01
3 Industry Sector 03
Here we can see that CustomerID 2 did not answer the Industry Sector Question hence no record in table 2 for that question.
I need to be able to display all the customers and their questions and answers but also include Questions they didn't answer, so for example i need to display something like this...
CustomerID Question Answer
1 Job Function District Nurse
1 Specialist Areas Diabetes
1 Industry Sector Primary Care
2 Job Function Health Visitor
2 Specialist Areas Wound Care
2 Industry Sector <Not Answered>
3 Job Function Practice Nurse
3 Specialist Areas Oncology
3 Industry Sector Private Sector
This shows CustomerID 2 with an Industry Sector of <Not Answered> so the user will be able to select <Not Answered> and see which customers have missed which questions.
I have been trying to use some kind of Join in my script but to no avail
Any ideas about how best to tackle this would be much appreciated.
Go to Solution.
Here are three different and simpler approaches. I suspect there's a simpler way still, which is why I have so many attempts, but I'm giving up for now. My favorite so far is the second:
LEFT JOIN (Tab2)LOAD *RESIDENT Tab1;All:LOAD num(fieldvalue('CustomerID',recno())) as CustomerIDAUTOGENERATE fieldvaluecount('CustomerID');LEFT JOIN (All)LOAD text(fieldvalue('Question',recno())) as QuestionAUTOGENERATE fieldvaluecount('Question');OUTER JOIN (Tab2)LOAD *RESIDENT All;Tab3:NOCONCATENATELOAD CustomerID,Question,alt(Choice,00) as Choice,if(len(Answer),Answer,'<Not Answered>') as AnswerRESIDENT Tab2;DROP TABLES All, Tab1, Tab2;
Have a look at this Example.
I hope this will solve your problem.
That's fantastic guys - thankyou very much.
Going to spend today looking at how they work