Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
stabben23
Honored Contributor

Connect with timestamps

Does anyone have experience to Connect data With timestamp which not match. I need a function so I can map a Field(Dim) into another table.

This is two machines, machine1 has a start and end time, machine1 producing pieces to machine2, machine2 also have a start and end time. Machine1 always start first even if starttime on machine2 has a previous starttime. Machine2 end is when everything is produced and a New cycle begins.

You dont need to solve evrything here, just point me in the right direction on what function I should use here.

As you can see there is sometimes overlapping times, but the logic is as above mentioned.

13 Replies

Re: Connect with timestamps

try after removing time stamp from all date fields and then associate.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
stabben23
Honored Contributor

Re: Connect with timestamps

Hi Prashant,

I need timestamp, the reason is that I want to calculate Production yield on every Dim cycle.

If I remove time, there will be many to many Association.

Re: Connect with timestamps

Hi,

can you post required output.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
stabben23
Honored Contributor

Re: Connect with timestamps

Re: Connect with timestamps

what was the logic behind 3 and 4 th line output????

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.

Re: Connect with timestamps

If there is always similar no of rows in machine 1 and machine 2

then you while loading data you can use rowNo() to assign row no to each row, then associate with row no()

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
effinty2112
Honored Contributor

Re: Connect with timestamps

Hi Steffan,

Please clarify the association between the two time intervals.

For Dim value 50-150 the interval [mintime, maxtime] in contained in [start, end].

For 63-125 neither interval is contained in the other, there is an overlap.

If an overlap is enough to give an association then the interval

[mintime, maxtime] = 2017-08-23 22:35 to 2017-08-24 00:09


overlaps the first two [start, end] intervals.


2017-08-22 14:45 to 2017-08-23 22:42

and

2017-08-23 22:43 to 2017-08-24 00:25


Cheers


Andrew

stabben23
Honored Contributor

Re: Connect with timestamps

No, the rows are not similar and can not be compared With each other.

stabben23
Honored Contributor

Re: Connect with timestamps

Hi Andrew,

I'll try to clarify, End time on machine2 is when the cycle is finished. Start time on machine2 will then be the same on next cycle. Start time on machine2 is like the machine is now ready for next cyckle. End time is when they have received the last piece and reset the cycle.

In this example 63-125 start at 2017-08-23 22:35 in machine1 and the last piece was received 2017-08-24 00:25.

Machine1 start a New cycle (44-85) 2017-08-24 00:16 but will not received to machine2 until 2017-08-24 00:25

63-1252017-08-23 22:352017-08-24 00:0963-1252017-08-23 22:422017-08-24 00:25
44-852017-08-24 00:162017-08-24 07:1644-852017-08-24 00:252017-08-24 07:33

This is tricky, I know, but some of my idea is if I can in somehow "point" in the middle of the timestamps and Connect the machine timestamps With each other.