topic Re: Duplicate Data in QlikView

Duplicate Data

Fri, 11 Oct 2013 12:10:05 GMT

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

Re: Duplicate Data

Fri, 11 Oct 2013 12:29:06 GMT

Witch if the table store duplicates? No1 or No2?

Example of the data would be helpful.

Re: Duplicate Data

Fri, 11 Oct 2013 12:35:37 GMT

Hi Alexey,

Its the WO that is the duplicate (it sits in both table 1 & 2).

I have attached an example of the data. I hope this helps.

Re: Duplicate Data

Fri, 11 Oct 2013 12:41:14 GMT

while using date in the chart apply only(Distinct date_field)

It will not duplicate the dates.

Re: Duplicate Data

ecahen — Fri, 11 Oct 2013 12:42:47 GMT

hi, can you send me your qvw file?

Re: Duplicate Data

Fri, 11 Oct 2013 15:26:22 GMT

Sure. How is the best way to send it to you?

Re: Duplicate Data

Fri, 11 Oct 2013 15:27:38 GMT

Hi Hannah,

Apologies, but how would I do that exactly?

Thank you for your reply.

Susan

Re: Duplicate Data

ecahen — Fri, 11 Oct 2013 15:43:42 GMT

If the file is not to heavy, simply by uploading it. Other why you can empty some dimension before.

Thanks;

Edouard Cahen

Directeur Associé - <mailto:ecahen@excelcio.com> ecahen@excelcio.com

Description : Description : Signature Ted

Excelcio est une société spécialisée en pilotage de la performance <http://www.excelcio.com/> www.excelcio.com

De : Susan Horn

Envoyé : vendredi 11 octobre 2013 17:27

À : Edouard CAHEN

Objet : Re: - Duplicate Data

<http://community.qlik.com/> QlikCommunity

Duplicate Data

reply from Susan Horn <http://community.qlik.com/people/SusanHorn?et=watches.email.thread> in New to QlikView - View the full discussion <http://community.qlik.com/message/404926?et=watches.email.thread#404926>

Re: Duplicate Data

Peter_Cammaert — Fri, 11 Oct 2013 15:47:31 GMT

Lift the unique identification of the oldest records by using the GROUP BY clause and the min() function, like this:

Entries:

LOAD UniqueReferenceNr, Min(DateOfRecord) as CreationDate FROM File.qvd GROUP BY UniqueReferenceNr;

Then JOIN the original table to Entries to add further details to the record identifiers you want to keep.

Sorry for the brevity. If you post your document in this discussion, we could provide you with a ready example.

Best,

Peter

Re: Duplicate Data

Mon, 14 Oct 2013 08:13:19 GMT

I am having problems attaching documents on the forum, so fingers crossed this has worked.

Thank you for your help and advice so far. Much appreciated.

Re: Duplicate Data

Peter_Cammaert — Mon, 14 Oct 2013 10:00:03 GMT

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

Re: Duplicate Data

Mon, 14 Oct 2013 15:27:48 GMT

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?

Re: Duplicate Data

rustyfishbones — Mon, 14 Oct 2013 15:39:06 GMT

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

Re: Duplicate Data

Peter_Cammaert — Tue, 15 Oct 2013 08:43:07 GMT

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

Re: Duplicate Data

Thu, 17 Oct 2013 08:44:52 GMT

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

Re: Duplicate Data

Thu, 17 Oct 2013 08:46:39 GMT

Hi Alan,

Thank you for your suggestion.

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

Thank you

Re: Duplicate Data

Peter_Cammaert — Thu, 17 Oct 2013 08:56:19 GMT

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

Re: Duplicate Data

Thu, 17 Oct 2013 09:12:09 GMT

I have just added the excel document mentioned

I am going to try your suggestion now.

Your help is much appreciated Peter.

Susan

Re: Re: Duplicate Data

Peter_Cammaert — Thu, 17 Oct 2013 11:38:36 GMT

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

Re: Re: Duplicate Data

Thu, 17 Oct 2013 15:57:55 GMT

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