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]

Just a question. Every record with a unique WO occurs only once in your table WKAW, correct? Then the field "originator" already has the login id of the person that created the record, correct?

If not, you'll have to change the SELECT statement for table WKAWS so that only the oldest record is retained. Does that table include a timestamp of the login of person "login_id" for workorder "wo"? If it does, your code can become something like:

No2:

SQL SELECT "login_id",  wo, timestamplogin
FROM MainSaver11.dbo.wkaws;
store No2 into No2.qvd(qvd);
drop table No2;

SingleLoginID:
LOAD wo, FirstSortedValue(login_id, timestamplogin) as login_id
FROM No2.qvd (qvd)

GROUP BY wo;

Make sure that the timestamp in table WKAWS actually includes a time part, otherwise you'll end up with many login-id's again.

Best,

Peter

[#3]

Hi Peter,

FYI - The unique WO's that occur in the table WKAW are attached to an 'Originator'.

The 'Login_ID'  that you find in the table WKAWS is attached to the login-id of the person logged into system at the time, who raises that WO (therefore the Login_ID is not necessarily the Origiantor).

For example, I can raise a WO and my Login-ID against that WO will be recorded as 'Susan Horn'. I can however add in the Orirginator of that WO as 'Joe Bloggs' and therefore the 'Originator' against that WO will be recorded as 'Joe Bloggs' (please see attached WO and specific 'Originator' field I am making reference too).

Does that make sense?

[rustyfishbones]

Hi Susan,

Have you tried using any of the following

FIRSTSORTEDVALUE

LASTSORTEDVALUE

MAXSTRING

MINSTRING

These videos may help you

Qlikview FIRSTVALUE & LASTVALUE in Script by RFB 152 - YouTube

https://www.youtube.com/watch?v=0Eye7_tKU0M

[Peter_Cammaert]

Perfectly clear. Upon inspecting your data, I saw that Originator may or may not appear in the list of login_ids.

If your DB Table WKAWS contains a timestamp for each login_id that makes a change to the wo, then you can use my SingleLoginID suggestion to attach the creator to the WO.

Let me know if you need any more help.

Peter

[#6]

Hi Peter,

Apologies for the late reply.

The WKAWS table has a separate date and time (see attached excel spreadsheet I have pulled information into). I can also pull out an end date and time.

Before I begin trying your suggestion, i thought it be better to check that this will work first

Thank you for your help.

Susan

[#7]

Hi Alan,

Thank you for your suggestion.

If Peter's suggestion is not possible, i will begin investigating this.

Thank you

[Peter_Cammaert]

Susan,

it will work, I am sure about that.

However, I don't see an Excel yet.

What you can do is modify my code so that Date and Time are loaded into table No2 (call them WKAWS.Date and WKAWS.Time) and merged into one in the call to FirstSortedValue() like

   ... FirstSortedValue(login_id, date#(WKAWS.Date & ' ' & WKAWS.Time)) ...

Good luck,

Peter

[#9]

I have just added the excel document mentioned

I am going to try your suggestion now.

Your help is much appreciated Peter.

Susan

[Peter_Cammaert]

OK here goes. In my earlier example code,

• Replace timestamplogin in the SQL SELECT with:
  
  start_date, start_time
  
  to load the date and time value into table No2.
• Replace the call to FirstSortedValue() in the LOAD for SingleLoginID with:
  
  FirstSortedValue(login_id, date#(start_date & ' ' & start_time, 'D/MM/YYYY 0:00 hhmm'))
  
  to pick the first login Id for a specific WO based on the timestamp

and you should be set.

Peter

[#11]

Hi Peter,

Thank you! That seems to be working great.

My only problem now is i would like to add the LOGIN_ID as a column into my table box. Is there anyway to do this?

Susan