Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Wlescrauw
Contributor
Contributor
‎2019-09-16 08:24 AM

Remove duplicates

Hi everyone,

I'm making a monitor for our factory on which a supervisor can see if a certain activity has been done on time or not.  To know if an item was on time or not I need to compare 2 timestamps from different tables with eachother, it then states of the item was on time or not - easy to make with a join and a calculated field.

However in the system every movement of an item gets a seperate timestamp and because of this every item has several different movements and different timestamps. This is causing issues for my monitor as I can only show every item once. I need Qlik to only count the latest timestamp of a certain reference as this is the final movement. I have been searching for days to find a solution but so far I have not been able to find anything that works... I am at the end of my knowledge and searching capabilities so I hope the great community here can help me out!

This is an example of the data which I currently receive:

Car Kit ID (Table 1 & 2 same column information)Deadline Tact (Table1)Physical Movement (Table2)On Time?
6141U4115112/09/2019 8:21:002/09/2019 08:05:45YES
0054530609U4100312/09/2019 8:21:002/09/2019 8:50:51NO
0054530609U4100312/09/2019 8:21:002/09/2019 10:20:36NO
0054530609U4100412/09/2019 8:21:002/09/2019 7:20:23YES
0054530609U4100412/09/2019 8:21:002/09/2019 8:50:00NO
0054530609U4200112/09/2019 9:42:002/09/2019 9:32:02YES
0054530609U4200112/09/2019 9:42:002/09/2019 9:40:12YES

 

The result I would like to get:

Car Kit ID (Table 1 & 2 same column information)Deadline Tact (Table1)Physical Movement (Table2)On Time?
6141U4115112/09/2019 8:21:002/09/2019 08:05:45YES
0054530609U4100312/09/2019 8:21:002/09/2019 10:20:36NO
0054530609U4100412/09/2019 8:21:002/09/2019 9:50:00NO
0054530609U4200112/09/2019 9:42:002/09/2019 9:40:12YES

 

Anybody wh

1,021 Views
0 Likes
1 Solution

Accepted Solutions
asinha1991
Creator III
Creator III
‎2019-09-16 08:55 AM

just inner join your table with max time

something like this

TABLE2_FINAL:

Load *  from TABLE2;

inner join 

Load max(Physical Movement) as Physical Movement,Car Kit ID from Table2 group by  Car Kit ID ;

View solution in original post

1,000 Views
0 Likes
3 Replies
Wlescrauw
Contributor
Contributor
‎2019-09-16 08:26 AM
Author

The message cut short so I'll add it here:

Anybody who can help me with this problem? I am getting a big headache because of this 🙂

Thanks in advance for your help!
Kind Regards,
Wouter

1,015 Views
0 Likes
asinha1991
Creator III
Creator III
‎2019-09-16 08:55 AM

just inner join your table with max time

something like this

TABLE2_FINAL:

Load *  from TABLE2;

inner join 

Load max(Physical Movement) as Physical Movement,Car Kit ID from Table2 group by  Car Kit ID ;

1,001 Views
0 Likes
Wlescrauw
Contributor
Contributor
‎2019-09-17 06:53 AM
Author

In response to asinha1991

Really that simple ... thanks for the help!

970 Views
0 Likes