Time Dimensions Difference

[michael_anywar]

Dear all, I need help on this, hoping its possible

ID	Start Date *Time Stamp1	End Date *TimeStamp2	Name
1	12/2/2012 12:22:42	12/2/2012 12:01:21	Activity1
2	12/2/2012 12:01:21	12/3/2012 16:01:21	Activity 2
3	12/3/2012 16:01:21	12/6/2012 12:01:21	Activity 3
4	12/6/2012 12:01:21	....	Activity 4

I would like to get the values of End Date (TimeStamp 2) taking into consideration that the values of TimeStamp2 are dependent on the valueTime Stamp1 of the next row and are same values only that they are values of the the second Row.

SO basically the first value of Row 1  TimeStamp2 would be 12/2/2012 12:01:21 provided the ID "2" is the one that comes next after ID "1" 

[michael_anywar]

Hi Mark, would the load work if I had Thousands of records in the Master table..?

Probably the loading inline of the data would take lots of resources...

So am skeptical on loading the data inline..

[ID, START, NAME];

NoConcatenate

NEW:

LOAD

    ID,

    START,

    IF(ROWNO() =1,

          '',

          IF(PEEK(NAME) = NAME,

          PEEK(START,-1),

          '')

    ) AS END,

    NAME

Resident DATA

ORDER BY NAME, ID desc

[michael_anywar]

Could I be doing something Wrong..

This is Based on my real Master table:

[TaskActivities]:

LOAD

Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [Start],

[fullName] AS [fullName],

[activityType] AS [activityType],

[taskType] AS [taskType],

[manufacturingOrderId] AS [manufacturingOrderId],

[productId] AS [productId],

[taskId] AS [taskId]

RESIDENT RestConnectorMasterTable;

// Left Join(root)

//     LOAD [ts] AS [End], [taskId] AS NextID

    NoConcatenate

NEW:

LOAD

    [taskId],

    Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [Start],

    IF(ROWNO() =1,

          '',

          IF(PEEK([fullName]) = [fullName],

          PEEK(START,-1),

          '')

    ) AS End_Date,

    [fullName],

    [taskType],

    [productId]

Resident RestConnectorMasterTable

ORDER BY End_Date, [taskId] desc;

DROP TABLE RestConnectorMasterTable;

[Mark_Little]

HI Michael,

Where data come from should matter, I just didn't that for data to test with.

The important ting is to get the data in the correct order to create the end date from.

So how are you grouping together? The example i gave was fullName, because you only wanted to take the the prior start date when the fullName value matched. Then it was by start date. We use desc order, because peek(field,-1) look at previous value in that field and you wanted the next value, so reversing the order achieves that.

So i would say that you want to Change the Order by to fullName, StartDate desc.

Mark

[michael_anywar]

Hello Mark,

See below, I've tried but giving me errors still. I dodnt know if am asking too much..am just getting stuck...

RestConnectorMasterTable:

SQL SELECT

"ts",

"fullName",

"activityType",

"taskType",

"manufacturingOrderId",

"productId",

"taskId"

FROM JSON (wrap on) "root"

WITH CONNECTION(Url "££££££$%^^")

NEXT startAt;

[root]:

LOAD

Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [StartTime],

[fullName] AS [fullName],

[activityType] AS [activityType],

[taskType] AS [taskType],

[manufacturingOrderId] AS [manufacturingOrderId],

[productId] AS [productId],

[taskId] AS [taskId]

RESIDENT RestConnectorMasterTable

// DROP TABLE RestConnectorMasterTable;

[root1]:

Load

NoConcatenate

    [StartTime],

    [fullName],

    [activityType],

RESIDENT root;

     Order by StartTime

     Drop Table root;

TaskActivities:

LOAD

     NoConcatenate

  [StartTime],

  [StartTime] as [EndTime],

    [fullName],

    [activityType],

    [manufacturingOrderId],

    [taskType],

    [taskId]

     Resident root1;

     group by fullname, EndTime;

     Drop table root1 ;

[Mark_Little]

HI Michael,

No problems. Part of the issue is your NoConatenate should always come before you load statement not after, like a join.

QlikView will automatically concatenate tables that contain the same field, so when you don't want that like in the example we us NoConcatenate to stop this.

You also seem to have removed the peek as well.

Mark