Fill missing data

[Santsoh_Qlik]

Hi,

Can this be done in Qlik ?

Source Data (table): 

Person	course	status
11	c1	complete
11	c2	In-progress
22	c1	complete

Anticipated Result (Pivot on Course):

I am try to pivot on course. Person# 22 is not required to take the course C2 and he does not have records in source feed. My goal in the report is to display "not applicable" or some text and not a dash (-).

	C1	C2
Person
11	complete	In-progress
22	complete	Not applicable

Note: It is hard for us to load and maintain a table for all not-applicable scenarios.  Checking it to see whether it can be dynamically handled with in the Qlik report. 

Appreciate your help. Thank you!! 

[gbommisetti]

Table:
LOAD * INLINE [

Person, course, status
11, c1, complete
11, c2, In-progress
22, c1, complete

];
courses:

Load Distinct course as course Resident Table;

Let vTemp = FieldValueCount('course');

For i=0 to vTemp-1

Let vTemp2 = Peek('course',$(i),'courses');

Final:

Load

Person,
status as '$(vTemp2)'
Resident Table
Where course = '$(vTemp2)'
;

Next

Drop Table courses;

CombineTable:

Load Distinct Person Resident Table;

Drop Table Table;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable = TableName($(i));

IF WildMatch('$(vTable)', 'Final*') THEN

LEFT JOIN ([CombineTable]) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i

NullAsValue *;

Set NullValue = 'Not applicable';
NoConcatenate
Test:
load *
Resident CombineTable;

drop Table CombineTable;

[Santsoh_Qlik]

Hi,

Thank you for your response. There are 100's of courses and can not create a column for each course.

If handling in the data model is the best way, then I would prefer below. 

Person	course	status
11	c1	complete
11	c2	In-progress
22	c1	complete
22	c2	Not applicable	New row

[gbommisetti]

Table:
LOAD * INLINE [

Person, course, status
11, c1, complete
11, c2, In-progress
22, c1, complete

];
Table2:
load distinct course
resident Table;
join
load distinct Person
resident Table;

Table3:
NoConcatenate
load *
Resident Table;
right join
load *
Resident Table2;

final:
NoConcatenate
load Person,
course,
if(isnull(status)=0 and status <> '',status,'Not Applicable') as status
Resident Table3;

drop tables Table,Table2,Table3;