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" 

[kaushiknsolanki]

Hi Assuming that these are the fields of your table you can try below script.

Data:

Load ID,StartDate,EndDate,Name

From XYZ;

Left join (Data)

Load StartDate as EndDate, ID as NextID

From xyz;

Regards,

Kaushik Solanki

[michael_anywar]

I forgot to mention, that the field TimeStamp 2 does not exist, so if I load it now, it will tell me the field  does not exists.

Probably i can re phase my problem that i need to create/ Add another field called End date with the specified data.

[kaushiknsolanki]

Try this then.

Load Date as StartDate,Previous(Date) as EndDate

From xyz

order by Date Desc;

Regards,

Kaushik Solanki

[michael_anywar]

Hello Solanki but Previous(date) dooes not exist. See table below. How I would like the out come to be.

ID and Start  have unique values so i need to make the values of field (Start) Mapped to a New field (End), provided that ID belongs to the same Filed (Name( and Field (Start) in the next Row.

ID	Start	End	Name
A	1	2	Name1
B	2	3	Name1
C	3		Name1
D	4	9	Name2
E	5	7	Name 3
F	9		Name 2
G	7	10	Name3
H	10		Name 3

NOTE: Field (End ) does not exist in the Master Table.

[Mark_Little]

Hi Micheal,

You want to look at the Peek Function.

Something Like the below

LOAD

     ID

     START

     IF(ROWNO() =1,

          '',

          PEEK(START,-1)

     ) AS END

     NAME;

FROM TABLE

ORDER BY ID desc;

The order by is important as you want the table in the reverse order.

Mark

[kaushiknsolanki]

Hi,

Can you tell me what all fields you have?

ID,Start and Name?

Regards,
Kaushik Solanki

[michael_anywar]

[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;

That is what I have. Now i need to add a new field End  basing on the criteria as I mentioned. Start and taskID  are Unique,

[michael_anywar]

Hi Mark, am not sure if the table is in Reverse, if we take it the way you suggest, then it will populate End that belongs to Name1 with values that belong to Name2, for example where ID is C in the previous table i have provided, yet END for ID C is supposed to be empty as Name1 didnt have any activity following ID C

[Mark_Little]

Hi,

Try the Below. Attached test.qvw

DATA:

LOAD * Inline

[ID, START, NAME

A, 1, Name1

B, 2, Name1

C, 3, Name1

D, 4, Name2

E, 5, Name3

F, 9, Name2

G, 7, Name3

H, 10, Name3

];

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;

DROP TABLE DATA;