Create copy for every record

[#1]

Hi, I have 2 tables loaded in qvw.

empid	empname
1	employee1
2	employee2
3	employee3
4	employee4

empid	training	trainingstatus
1	training a	complete
1	training b	complete
2	training b	in progress
3	training a	complete

This dashboard shows training completion across employees. I want to show that emloyee4 is not registered with both training. What happens when i load them is, emplooyee4 shows only not registered.  I would like to have every employee have records depending on how many training are available. I would like to have a data model that can create an output table like this:

empid	empname	training	trainingstatus
1	employee1	training a	complete
1	employee1	training b	complete
2	employee2	training b	in progress
2	employee2	training a	not registered
3	employee3	training a	complete
3	employee3	training a	not registered
4	employee4	training a	not registered
4	employee4	training b	not registered

I would like to achieve this without the data model suffering.

Thank you in advance for any help.

[Anil_Babu_Samineni]

Without model suffering means are you expecting front-end??

[#3]

Sorry. What I mean is that I will still have the employee table intact.

[neelamsaroha157]

Check this out

[Digvijay_Singh · Accepted solution]

Check this out -

I would not suggest to add empname in the newly created table to avoid synthetic key, it is already associated in the model through empid.

Employee:

Load * inline [

empid empname

1 employee1

2 employee2

3 employee3

4 employee4 ](delimiter is spaces);

training:

Load * inline [

empid, training, trainingstatus,

1, training a, complete

1, training b, complete

2, training b, in progress

3, training a, complete ];

Temp1:

Load empid

resident Employee;

Join

Load training

resident training;

left join(Temp1)

Load *

resident training;

NoConcatenate

Final:

Load empid,

  training,

  if(isnull(trainingstatus),'Not registered',trainingstatus) as trainingstatus

resident Temp1;

Drop table Temp1,training;

[#6]

Thanks! That worked