-
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
-
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
-
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.
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.
-
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 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
-
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;
-
Re: Time Dimensions Difference
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
-
Re: Time Dimensions Difference
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;
-
Re: Time Dimensions Difference
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
-
Re: Time Dimensions Difference
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 ;
-
Re: Time Dimensions Difference
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
-
-
-
-
-
-
-
Re: Time Dimensions Difference
Hi,
Can you tell me what all fields you have?
ID,Start and Name?
Regards,
Kaushik Solanki-
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,
-
-
-
-
-