从现有表格迭代以创建另一个表格 - Qlikview
如果标题不够清晰,但在Qlikview中可以做到这一点,我表示歉意吗?从现有表格迭代以创建另一个表格 - Qlikview
这是从数据库加载的原始表。
Variable Status Date Duration (Hours)
A StatusA 9/10/2017 18:30:00.00 4
A StatusB 9/10/2017 23:30:00.00 5
B StatusA 9/10/2017 10:00:00.00 8
B StatusB 9/10/2017 21:45:00.00 9
我想如何处理。
Variable Status Date Duration (Hours) FinishDate
A StatusA 9/10/2017 18:30:00.00 4 9/10/2017 22:30:00.00
A StatusB 9/10/2017 23:30:00.00 0.5 9/10/2017 23:59:59.59
A StatusB 9/11/2017 0:00:00.00 4.5 9/11/2017 3:30:00.00
B StatusA 9/10/2017 10:00:00.00 8 9/10/2017 18:00:00.00
B StatusB 9/10/2017 21:45:00.00 2.25 9/10/2017 23:59:59.59
B StatusB 9/11/2017 0:00:00.00 6.75 9/11/2017 6:15:00.00
我知道这是可能通过其驻台,但如何检查变量的运行时间超过一天,然后创建另一行时间分开,次日。这是因为我有一个星期的过滤器,所以如果最后一天(或周六)有一个变量超过持续时间,数据将不准确。
结果数据将需要获得每变量的总持续时间,按周过滤。
脚本:
T1:
LOAD *, timestamp(Timestamp+[Duration Hours]/24) as FinishDate;
LOAD *, timestamp(Timestamp#(left(Date,19),'DD-MM-YYYY hh:mm:ss')) as Timestamp;
LOAD
[EquipmentID] AS [Equipment ID],
[TransactionDate] AS [Date],
[LotID] AS [Lot ID],
[Status] AS [Status],
[DurationHours] AS [Duration Hours];
SQL
SELECT *
FROM [SQL_SourceDB].[dbo].[SourceTable]
WHERE [TransactionDate] >= '2016-01-01 00:00:00.000' AND [TransactionDate] <= '2016-01-31 00:00:00.000';
Left Join // add a split-flag where needed
LOAD Distinct Timestamp,FinishDate,fabs(Date(left(FinishDate,10))-Date(left(Timestamp,10)) >=1) as SplitFlag
Resident T1;
T2: // load first part (current day) of split-flag=1
LOAD
[Equipment ID],
[Timestamp] AS [Date],
[Lot ID],
[Status],
round((DayEnd(Timestamp)-Timestamp)*24,0.1) AS [Duration Hours]
Resident T1
Where SplitFlag=1;
Concatenate // load second part (next day) where split-flag=1
LOAD
[Equipment ID],
daystart(FinishDate) AS [Date],
[Lot ID],
[Status],
round((FinishDate-daystart(FinishDate))*24,0.1) AS [Duration Hours]
Resident T1
Where SplitFlag=1;
Concatenate // add the rest of the data (split-flag=0)
LOAD * Resident T1 Where SplitFlag=0;
DROP Table T1;
这应该工作(参见代码中的注释):
T1: // first need to set a proper timestamp and FinishDate
Load *, timestamp(Timestamp+[Duration (Hours)]/24) as FinishDate;
Load *, timestamp(Timestamp#(left(Date,19),'DD-MM-YYYY hh:mm:ss')) as Timestamp;
LOAD * INLINE [
Variable, Status, Date, Duration (Hours)
A, StatusA, 10-09-2017 18:30:00.00, 4
A, StatusB, 10-09-2017 23:30:00.00, 5
B, StatusA, 10-09-2017 10:00:00.00, 8
B, StatusB, 10-09-2017 21:45:00.00, 9
];
left join // add a split-flag where needed
Load Distinct Timestamp,FinishDate,fabs(Date(left(FinishDate,10))-Date(left(Timestamp,10)) >=1) as SplitFlag
Resident T1;
T2: // load first part (current day) of split-flag=1
Load Variable,Status,
\t Timestamp,
\t dayEnd(Timestamp) as FinishDate,
\t round((DayEnd(Timestamp)-Timestamp)*24,0.1) as [Duration (Hours)]
Resident T1
where SplitFlag=1;
Concatenate // load second part (next day) where split-flag=1
Load Variable,Status,
\t daystart(FinishDate) as Timestamp,
\t FinishDate,
\t round((FinishDate-daystart(FinishDate))*24,0.1) as [Duration (Hours)]
Resident T1
where SplitFlag=1;
Concatenate // add the rest of the data (split-flag=0)
Load * Resident T1 Where SplitFlag=0;
drop table T1; \t
嗨,我已经添加了上面的脚本。它似乎没有从T1中获取任何东西。它说,'T1
检查你的SQL日期格式加载时到QV(也许它是一个数字) - 这是唯一可以使这不起作用的东西。你可能不需要这行“LOAD *,timestamp(Timestamp#(left(Date,19),'DD-MM-YYYY hh:mm:ss'))作为时间戳;”或者你可能需要将左边(Date,19)更改为别的... 当我运行它时,它工作正常 – EldadT
来自数据库的日期格式为'2014-10-01 19:01:44.383'。我认为这只是时间戳。我会看看情况是否如此。 –
检查出来的 “间隔” 功能 – EldadT
@EldadT我有另外一个相关的问题以上,请检查:https://*.com/questions/46400966/add-and-duduct-on-datetime-qlikview –
行..检查我的答案低于 – EldadT