topic Re: Create copy for every record in QlikView

Create copy for every record

Tue, 02 May 2017 15:02:26 GMT

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.

Re: Create copy for every record

Anil_Babu_Samineni — Tue, 02 May 2017 16:18:09 GMT

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

Re: Create copy for every record

Tue, 02 May 2017 16:36:32 GMT

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

Re: Create copy for every record

neelamsaroha157 — Tue, 02 May 2017 17:34:51 GMT

Check this out

Re: Create copy for every record

Digvijay_Singh — Tue, 02 May 2017 18:03:12 GMT

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;

Re: Create copy for every record

Wed, 17 May 2017 15:49:05 GMT

Thanks! That worked