Duplicate Data

[#1]

No1:
SQL SELECT assetno,
    assetshortdesc,
    "org_date",
    "orig_priority",
    originator,
    "plan_priority",
    wo,
    "work_type"
FROM MainSaver11.dbo.wkaw;
store No1 into No1.qvd(qvd);
drop table No1;
Directory;
LOAD assetno, 
     assetshortdesc, 
     date (org_date) as org_date, 
     orig_priority, 
     upper (originator) as originator, 
     plan_priority, 
     wo, 
     work_type
FROM
No1.qvd
(qvd)
WHERE(org_date >= '01/01/2013')
and left (wo,1)<>'P' and left (wo,1)<>'T';
No2:
SQL SELECT "login_id",
    wo
FROM MainSaver11.dbo.wkaws;
store No2 into No2.qvd(qvd);
drop table No2;
Directory;
LOAD login_id, 
     wo
FROM
No2.qvd
(qvd);

Hi,

I am very new to Qlikview, so please be patient

I have loaded my data into Qlkiview (see above), but have just come up against a problem with duplication of records.

I know why this is, but I am unsure how to to go about fixing it.

I have loaded data from a call logging system and for every call logged a unique reference number is created.

When a call is logged the system logs the users LOGIN ID name details. Then for every time a user makes a change to the call (for example completes or closes the call) the system logs the users LOGIN ID name again.

Qlikview is currently showing me (for example) x3 of each unique reference number and a different LOGIN ID for each time a change was made.

How do I get Qlkiview to only show me the first initial record of the LOGIN ID on creation of the job, not the each individual change?

I hope this makes sense.

Please let me know if there is anything else I can attach to help you answer my question.

FYI - I have attached an image of both tables I am using.

Thanks

[Peter_Cammaert]

Susan,

just add login_id to your table box. As long as a unique login_id (the one that created the WO) is connected to every wo value, the association will work out just fine. Table boxes do not have to contain just fields from the same table. Mix em up like you see fit.

Good luck,

Peter

[#3]

Thank you Peter!

I am so close (please do not get fed up with me ha).

Only problem i have now, is when i add the login-id to the table box, it duplicates all of the other fields (see attached).

What i am trying to do is create a table view that managers can use (by using the select fields) to view all wo's by a login-id.

I have also attached my script.

Susan

[Peter_Cammaert]

Susan,

change the last part of your script (starting with the table label LoginID:) into the following. Then run your script again and see what you get.

LoginID:

LOAD wo, FIRSTSORTEDVALUE(login_id,Date#(start_date&''&start_time,'DD/MM/YYYY 0:00 hhmm')) as login_id

FROM WKAWS.qvd(qvd)

GROUP BY wo;

What your were experiencing after your last change are the effects of a so-called Cartesian Product. There is no explicit link between wo and login_id. Therefore if you put the two in the same sheet object, QlikView will first combine every login_id value with every wo value (just like a JOIN) leading to N x M possible combinations (N = number of login_id's, M = number of different wo's). My modification will restore the explicit link so that every wo has a single associated login_id.

Good luck,

Peter

[#5]

I have done as you say. However, i now have the login-id field as blank and underneath some wo's without any information at all (see attached):

My guess is it has something to do with the WO's that do not have an orig_date of 01/01/2013 or after?

I tried to amend my script, see below and although it removed some of the wo's with no information, some still remain.

LoginID:
LOAD wo,FIRSTSORTEDVALUE(login_id,Date#(start_date&''&start_time,'DD/MM/YYYY 0:00 hhmm')) as login_id
FROM WKAWS.qvd(qvd)
WHERE start_date>='01/01/2013'
and left (wo,1)<>'P' and left (wo,1)<>'T'

GROUP BY wo;

Its frustrating knowing im so close....

[Peter_Cammaert]

Do you mean that login_id is completely blank, i.e. there are no wo's with a login_id value?

Can you open the Table Viewer, right-click on the header of table LoginID and select Preview. A dialog iwll open with values for wo and login_id. Can you post a snapshot of this dialog?

Thanks.

Peter

[#7]

Morning Peter,

Thats correct. The Login_ID column is completely blank all the way through the table. Then half the way down, only the WO's are visible.

I have attached as requested a snapshot of the preview information.

NB: I have added the field work_type (i dont think this is causing the problem?)

[Peter_Cammaert]

I guess the LoginID table isn't properly constructed. Most probably your Date#() call produces only NULL values and cannot find the oldest login_id for a wo. Can you check whether the date format string corresponds to the actual data that results from putting start_date and start_time together?

It may seem like a silly question, but is there a quoted space between start_date and start_time in the expression Date#(start_date & ' ' & start_time,'DD/MM/YYYY 0:00 hhmm')? This format string has to be identical to the result of start_date & ' ' & start_time!

Peter

[#9]

When i preview the start_time and date_time it looks like the attached.

Is this maybe why this isnt working?

P.s The space in the formula does exist

Susan

[Peter_Cammaert]

Replace the format string with the following: 'DD/MM/YYYY 00:00:00 hhmm'

Peter

[#11]

No luck im afraid Peter.

Im still experiencing the same issue.

Is it likley that the tables have not been constructed properly (as stated) as this will not work regardless?

I appreciate all of your help.

Susan