Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

michael_anywar
Contributor

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" 


Tags (1)
14 Replies

Re: Time Dimensions Difference

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
Contributor

Re: Time Dimensions Difference

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.

Re: Time Dimensions Difference

Try this then.

Load Date as StartDate,Previous(Date) as EndDate

From xyz

order by Date Desc;

Regards,

Kaushik Solanki

michael_anywar
Contributor

Re: Time Dimensions Difference

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.

mark6505
Valued Contributor III

Re: Time Dimensions Difference

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

Re: Time Dimensions Difference

Hi,

Can you tell me what all fields you have?

ID,Start and Name?

Regards,
Kaushik Solanki

michael_anywar
Contributor

Re: Time Dimensions Difference

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

Re: Time Dimensions Difference

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

mark6505
Valued Contributor III

Re: Time Dimensions Difference

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;

Community Browser