Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
michael_anywar
Creator
Creator

Time Dimensions Difference

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

IDStart Date *Time Stamp1End Date *TimeStamp2Name
112/2/2012 12:22:4212/2/2012 12:01:21Activity1
212/2/2012 12:01:2112/3/2012 16:01:21Activity 2
312/3/2012 16:01:2112/6/2012 12:01:21Activity 3
412/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" 


14 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
michael_anywar
Creator
Creator
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

Try this then.

Load Date as StartDate,Previous(Date) as EndDate

From xyz

order by Date Desc;

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
michael_anywar
Creator
Creator
Author

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.

IDStartEndName
A12Name1
B23Name1
C3Name1
D49Name2
E57Name 3
F9Name 2
G710Name3
H10Name 3

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

Mark_Little
Luminary
Luminary

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
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Can you tell me what all fields you have?

ID,Start and Name?

Regards,
Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
michael_anywar
Creator
Creator
Author

[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
Creator
Creator
Author

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
Luminary
Luminary

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;