Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have following subset of records from audit history table. I'm trying to rearrange data so I've start datetime and end datetime for records where there is a pair from NewValue and OldValue but OldValue has to match NewValue from earlier timestamp or TL Sequence # of OldValue record is higher than that of NewValue. Also if there are more than one pair of same value than it should match appropriately so that there is no duplication as well pairing is right.
I was planning to read/load CP Doc ID, EditDate as StartDate, Null() as EndDate, TL Sequence, FieldEvent, NewValue in one table and then do concatenate it with somehow matching appropriate record using FieldEvent, OldValue, EditDate, TL Sequence to assign EditDate as EndDate
I'm looking for help/guidance in this scripting functionality
CP Doc ID | EditDate | TL Sequence | FieldEvent | NewValue | OldValue |
488057 | 8/5/2016 12:36:00 PM | 657806 | Created | - | - |
488057 | 8/5/2016 1:34:00 PM | 657807 | DocStatus | UR by LDC | PR of LCSD |
488057 | 8/9/2016 10:22:00 AM | 657808 | DocStatus | HRC | UR by LDC |
488057 | 8/15/2016 11:44:00 AM | 657809 | DocStatus | PR of LCSD | HRC |
488057 | 9/8/2016 11:00:00 AM | 657810 | DocStatus | RS to LHR | PR of LCSD |
488057 | 9/8/2016 11:01:00 AM | 657811 | DocStatus | RS to LHR - HRUN | RS to LHR |
488057 | 9/8/2016 11:17:00 AM | 657812 | DocStatus | UR by LDC | RS to LHR - HRUN |
488057 | 9/8/2016 1:28:00 PM | 657813 | DocStatus | HRC | UR by LDC |
Thank you all for looking at this question and providing ideas.
I've included new data set that is more comprehensive of the scenario.
I found my solution by doing some programming logic in scripting. Currently I built logic for HistoryFieldEvent = 'DocStatus'
[CP_DocHistory_Data]:
LOAD [CP Doc ID] as CPDocID,
[Edit Date] as HistoryEditDate,
If(Trim([Field / Event]) = 'Created.', 'DocStatus', Trim([Field / Event])) as HistoryFieldEvent,
If(Trim([Field / Event]) = 'Created.', AutoNumber([CP Doc ID]&[Edit Date]&'NR'),
If(Trim([Field / Event]) = 'DocStatus', AutoNumber([CP Doc ID]&[Edit Date]&[New Value]), AutoNumber([CP Doc ID]&[Edit Date]& Trim([Field / Event])))) as [TL Sequence],
If(Trim([Field / Event]) = 'Created.', 'NR',
If(Trim([Field / Event]) = 'DocStatus', [New Value], Trim([Field / Event]))) as [TL Phase],
[Old Value] as HistoryOldValue,
If(Trim([Field / Event]) = 'Created.', 'NR', [New Value]) as HistoryNewValue
From [..\Data\CP_DocHistory_Data.qvd](qvd)
Where [CP Doc ID] = '217242' or
[CP Doc ID] = '314023' or
[CP Doc ID] = '360394' or
[CP Doc ID] = '488057' ;
[DocStatusCount]:
LOAD CPDocID,
Count([CPDocID]) as DocStatusCnt
Resident [CP_DocHistory_Data]
Where HistoryFieldEvent = 'DocStatus'
Group By CPDocID
Order By CPDocID;
[NextStepCount]:
LOAD CPDocID,
Count([CPDocID]) as NextStepCnt
Resident [CP_DocHistory_Data]
Where HistoryFieldEvent = 'Next Step'
Group By CPDocID
Order By CPDocID;
DistDocStatusDocIDCount:
LOAD Count(DISTINCT [CPDocID]) as DistDocStatusDocIDCount
Resident [DocStatusCount];
LET vDistDocStatusDocIDCount = PEEK('DistDocStatusDocIDCount',-1,'DistDocStatusDocIDCount') - 1;
DistNextStepDocIDCount:
LOAD Count(DISTINCT [CPDocID]) as DistNextStepDocIDCount
Resident [NextStepCount];
LET vDistNextStepDocIDCount = PEEK('DistNextStepDocIDCount',-1,'DistNextStepDocIDCount');
SET vCPDocID=0;
SET vTLSeq=0;
FOR DocStatusDocIDCounter = 0 to $(vDistDocStatusDocIDCount)
LET vCPDocID = Peek('CPDocID',$(DocStatusDocIDCounter),'DocStatusCount');
LET vDocStatusCnt = Peek('DocStatusCnt',$(DocStatusDocIDCounter),'DocStatusCount') - 1;
IF $(vDocStatusCnt) > 1 then
FOR DocStatusCountCounter = 0 to $(vDocStatusCnt)
CPDocHistTemp:
LOAD CPDocID,
Min([TL Sequence]) as TLSequence
Resident [CP_DocHistory_Data]
Where CPDocID = $(vCPDocID)
and HistoryFieldEvent = 'DocStatus'
and [TL Sequence] > $(vTLSeq)
Group By CPDocID
Order By CPDocID, [TL Sequence];
LET vTLSeq = Peek('TLSequence',0,'CPDocHistTemp');
LET vCPDID = Peek('CPDocID',0,'CPDocHistTemp');
DROP Table CPDocHistTemp;
CPDocHistTemp:
LOAD CPDocID,
[TL Sequence] as TLSequence,
HistoryEditDate,
HistoryFieldEvent,
[TL Phase] as TLPhase,
HistoryOldValue,
HistoryNewValue
Resident [CP_DocHistory_Data]
Where CPDocID = $(vCPDocID)
and [TL Sequence] = $(vTLSeq)
Order By CPDocID, [TL Sequence];
LET vStartDateTime = Peek('HistoryEditDate',0,'CPDocHistTemp');
LET vFieldEvent = Peek('HistoryFieldEvent',0,'CPDocHistTemp');
LET vPhase = Peek('TLPhase',0,'CPDocHistTemp');
LET vNewValue = Peek('HistoryNewValue',0,'CPDocHistTemp');
LET vOldValue = Peek('HistoryOldValue',0,'CPDocHistTemp');
DROP Table CPDocHistTemp;
CPDocHistTemp:
LOAD CPDocID,
Min([TL Sequence]) as TLSequence
Resident [CP_DocHistory_Data]
Where CPDocID = $(vCPDocID)
and HistoryFieldEvent = 'DocStatus'
and HistoryOldValue = '$(vNewValue)'
and [TL Sequence] > $(vTLSeq)
Group By CPDocID
Order By CPDocID, [TL Sequence];
LET vSecTLSeq = Peek('TLSequence',0,'CPDocHistTemp');
DROP Table CPDocHistTemp;
IF IsNum($(vSecTLSeq)) then
CPDocHistTemp:
LOAD CPDocID,
[TL Sequence] as TLSequence,
HistoryEditDate
Resident [CP_DocHistory_Data]
Where CPDocID = $(vCPDocID)
and [TL Sequence] = $(vSecTLSeq)
Order By CPDocID, [TL Sequence];
LET vEndDateTime = Peek('HistoryEditDate',0,'CPDocHistTemp');
[TLTable]:
LOAD *
Inline [CPDID, TLS, HFE, VALUE, STRATDTTM, ENDDTTM
$(vCPDocID), $(vTLSeq), $(vFieldEvent), $(vNewValue), $(vStartDateTime), $(vEndDateTime)];
DROP Table CPDocHistTemp;
ELSE
CPDocHistTemp:
LOAD CPDocID,
[TL Sequence] as TLSequence,
HistoryEditDate,
HistoryFieldEvent,
[TL Phase] as TLPhase,
HistoryOldValue,
HistoryNewValue
Resident [CP_DocHistory_Data]
Where CPDocID = $(vCPDocID)
and [TL Sequence] = $(vTLSeq)
Order By CPDocID, [TL Sequence];
LET vStartDateTime = Peek('HistoryEditDate',0,'CPDocHistTemp');
LET vFieldEvent = Peek('HistoryFieldEvent',0,'CPDocHistTemp');
LET vPhase = Peek('TLPhase',0,'CPDocHistTemp');
LET vNewValue = Peek('HistoryNewValue',0,'CPDocHistTemp');
LET vOldValue = Peek('HistoryOldValue',0,'CPDocHistTemp');
LET vEndDateTime = Null();
[TLTable]:
LOAD *
Inline [CPDID, TLS, HFE, VALUE, STRATDTTM, ENDDTTM
$(vCPDocID), $(vTLSeq), $(vFieldEvent), $(vNewValue), $(vStartDateTime), $(vEndDateTime)];
DROP Table CPDocHistTemp;
ENDIF;
NEXT DocStatusCountCounter;
ENDIF;
NEXT DocStatusDocIDCounter;
DROP Table DistDocStatusDocIDCount;
DROP Table DistNextStepDocIDCount;
Hi,
Would you happen to have sample data to share here?
Thanks
Hi,
It is the same data that I had included in the question but I'm including the excel file of it.
Vijay
Ok.
Something like this:
For this to work, you need to sort your data by EditDate and TL Sequence, and use Below() function.
Please see the attached.
Thanks
Hi,,
Thanks for your input but I'm looking to create a table in script that will look something like table provided below.
Best Regards,
CP Doc ID | TL Sequence | Phase | StartDate | EndDate |
488057 | 657806 | Created | 8/5/2016 12:36:00 PM | |
488057 | 657807 | UR by LDC | 8/5/2016 1:34:00 PM | 8/9/2016 10:22:00 AM |
488057 | 657808 | HRC | 8/9/2016 10:22:00 AM | 8/15/2016 11:44:00 AM |
488057 | 657809 | PR of LCSD | 8/15/2016 11:44:00 AM | 9/8/2016 11:00:00 AM |
488057 | 657810 | RS to LHR | 9/8/2016 11:00:00 AM | 9/8/2016 11:01:00 AM |
488057 | 657811 | RS to LHR - HRUN | 9/8/2016 11:01:00 AM | 9/8/2016 11:17:00 AM |
488057 | 657812 | UR by LDC | 9/8/2016 11:17:00 AM | 9/8/2016 1:28:00 PM |
488057 | 657813 | HRC | 9/8/2016 1:28:00 PM |
Hi,
Can you try this once...
arrange:
NoConcatenate
LOAD Min([CP Doc ID]) as [CP Doc ID],
Min(EditDate) as EditDate,
[TL Sequence] as [TL Sequence],
FieldEvent,
min(NewValue) as NewValue1,
min(OldValue) as OldValue1
FROM TableData.xlsx (ooxml, embedded labels, table is Sheet1)
group by FieldEvent, [TL Sequence];
left join
LOAD [CP Doc ID] as [CP Doc ID],
EditDate as EditDate1,
[TL Sequence]-1 as [TL Sequence],
FieldEvent as FieldEvent,
(NewValue) as NewValue,
OldValue as Phase
FROM TableData.xlsx (ooxml, embedded labels, table is Sheet1);
The resultant table will be below:
CP Doc ID | TL Sequence | FieldEvent | Start Date | End Date | NewValue | Phase |
---|---|---|---|---|---|---|
488057 | 657806 | Created | 8/5/2016 12:36:00 PM | |||
488057 | 657807 | DocStatus | 8/5/2016 1:34:00 PM | 8/9/2016 10:22:00 AM | HRC | UR by LDC |
488057 | 657808 | DocStatus | 8/9/2016 10:22:00 AM | 8/15/2016 11:44:00 AM | PR of LCSD | HRC |
488057 | 657809 | DocStatus | 8/15/2016 11:44:00 AM | 9/8/2016 11:00:00 AM | RS to LHR | PR of LCSD |
488057 | 657810 | DocStatus | 9/8/2016 11:00:00 AM | 9/8/2016 11:01:00 AM | RS to LHR - HRUN | RS to LHR |
488057 | 657811 | DocStatus | 9/8/2016 11:01:00 AM | 9/8/2016 11:17:00 AM | UR by LDC | RS to LHR - HRUN |
488057 | 657812 | DocStatus | 9/8/2016 11:17:00 AM | 9/8/2016 1:28:00 PM | HRC | UR by LDC |
488057 | 657813 | DocStatus | 9/8/2016 1:28:00 PM |
Thanks
Sujana
Thank you all for looking at this question and providing ideas.
I've included new data set that is more comprehensive of the scenario.
I found my solution by doing some programming logic in scripting. Currently I built logic for HistoryFieldEvent = 'DocStatus'
[CP_DocHistory_Data]:
LOAD [CP Doc ID] as CPDocID,
[Edit Date] as HistoryEditDate,
If(Trim([Field / Event]) = 'Created.', 'DocStatus', Trim([Field / Event])) as HistoryFieldEvent,
If(Trim([Field / Event]) = 'Created.', AutoNumber([CP Doc ID]&[Edit Date]&'NR'),
If(Trim([Field / Event]) = 'DocStatus', AutoNumber([CP Doc ID]&[Edit Date]&[New Value]), AutoNumber([CP Doc ID]&[Edit Date]& Trim([Field / Event])))) as [TL Sequence],
If(Trim([Field / Event]) = 'Created.', 'NR',
If(Trim([Field / Event]) = 'DocStatus', [New Value], Trim([Field / Event]))) as [TL Phase],
[Old Value] as HistoryOldValue,
If(Trim([Field / Event]) = 'Created.', 'NR', [New Value]) as HistoryNewValue
From [..\Data\CP_DocHistory_Data.qvd](qvd)
Where [CP Doc ID] = '217242' or
[CP Doc ID] = '314023' or
[CP Doc ID] = '360394' or
[CP Doc ID] = '488057' ;
[DocStatusCount]:
LOAD CPDocID,
Count([CPDocID]) as DocStatusCnt
Resident [CP_DocHistory_Data]
Where HistoryFieldEvent = 'DocStatus'
Group By CPDocID
Order By CPDocID;
[NextStepCount]:
LOAD CPDocID,
Count([CPDocID]) as NextStepCnt
Resident [CP_DocHistory_Data]
Where HistoryFieldEvent = 'Next Step'
Group By CPDocID
Order By CPDocID;
DistDocStatusDocIDCount:
LOAD Count(DISTINCT [CPDocID]) as DistDocStatusDocIDCount
Resident [DocStatusCount];
LET vDistDocStatusDocIDCount = PEEK('DistDocStatusDocIDCount',-1,'DistDocStatusDocIDCount') - 1;
DistNextStepDocIDCount:
LOAD Count(DISTINCT [CPDocID]) as DistNextStepDocIDCount
Resident [NextStepCount];
LET vDistNextStepDocIDCount = PEEK('DistNextStepDocIDCount',-1,'DistNextStepDocIDCount');
SET vCPDocID=0;
SET vTLSeq=0;
FOR DocStatusDocIDCounter = 0 to $(vDistDocStatusDocIDCount)
LET vCPDocID = Peek('CPDocID',$(DocStatusDocIDCounter),'DocStatusCount');
LET vDocStatusCnt = Peek('DocStatusCnt',$(DocStatusDocIDCounter),'DocStatusCount') - 1;
IF $(vDocStatusCnt) > 1 then
FOR DocStatusCountCounter = 0 to $(vDocStatusCnt)
CPDocHistTemp:
LOAD CPDocID,
Min([TL Sequence]) as TLSequence
Resident [CP_DocHistory_Data]
Where CPDocID = $(vCPDocID)
and HistoryFieldEvent = 'DocStatus'
and [TL Sequence] > $(vTLSeq)
Group By CPDocID
Order By CPDocID, [TL Sequence];
LET vTLSeq = Peek('TLSequence',0,'CPDocHistTemp');
LET vCPDID = Peek('CPDocID',0,'CPDocHistTemp');
DROP Table CPDocHistTemp;
CPDocHistTemp:
LOAD CPDocID,
[TL Sequence] as TLSequence,
HistoryEditDate,
HistoryFieldEvent,
[TL Phase] as TLPhase,
HistoryOldValue,
HistoryNewValue
Resident [CP_DocHistory_Data]
Where CPDocID = $(vCPDocID)
and [TL Sequence] = $(vTLSeq)
Order By CPDocID, [TL Sequence];
LET vStartDateTime = Peek('HistoryEditDate',0,'CPDocHistTemp');
LET vFieldEvent = Peek('HistoryFieldEvent',0,'CPDocHistTemp');
LET vPhase = Peek('TLPhase',0,'CPDocHistTemp');
LET vNewValue = Peek('HistoryNewValue',0,'CPDocHistTemp');
LET vOldValue = Peek('HistoryOldValue',0,'CPDocHistTemp');
DROP Table CPDocHistTemp;
CPDocHistTemp:
LOAD CPDocID,
Min([TL Sequence]) as TLSequence
Resident [CP_DocHistory_Data]
Where CPDocID = $(vCPDocID)
and HistoryFieldEvent = 'DocStatus'
and HistoryOldValue = '$(vNewValue)'
and [TL Sequence] > $(vTLSeq)
Group By CPDocID
Order By CPDocID, [TL Sequence];
LET vSecTLSeq = Peek('TLSequence',0,'CPDocHistTemp');
DROP Table CPDocHistTemp;
IF IsNum($(vSecTLSeq)) then
CPDocHistTemp:
LOAD CPDocID,
[TL Sequence] as TLSequence,
HistoryEditDate
Resident [CP_DocHistory_Data]
Where CPDocID = $(vCPDocID)
and [TL Sequence] = $(vSecTLSeq)
Order By CPDocID, [TL Sequence];
LET vEndDateTime = Peek('HistoryEditDate',0,'CPDocHistTemp');
[TLTable]:
LOAD *
Inline [CPDID, TLS, HFE, VALUE, STRATDTTM, ENDDTTM
$(vCPDocID), $(vTLSeq), $(vFieldEvent), $(vNewValue), $(vStartDateTime), $(vEndDateTime)];
DROP Table CPDocHistTemp;
ELSE
CPDocHistTemp:
LOAD CPDocID,
[TL Sequence] as TLSequence,
HistoryEditDate,
HistoryFieldEvent,
[TL Phase] as TLPhase,
HistoryOldValue,
HistoryNewValue
Resident [CP_DocHistory_Data]
Where CPDocID = $(vCPDocID)
and [TL Sequence] = $(vTLSeq)
Order By CPDocID, [TL Sequence];
LET vStartDateTime = Peek('HistoryEditDate',0,'CPDocHistTemp');
LET vFieldEvent = Peek('HistoryFieldEvent',0,'CPDocHistTemp');
LET vPhase = Peek('TLPhase',0,'CPDocHistTemp');
LET vNewValue = Peek('HistoryNewValue',0,'CPDocHistTemp');
LET vOldValue = Peek('HistoryOldValue',0,'CPDocHistTemp');
LET vEndDateTime = Null();
[TLTable]:
LOAD *
Inline [CPDID, TLS, HFE, VALUE, STRATDTTM, ENDDTTM
$(vCPDocID), $(vTLSeq), $(vFieldEvent), $(vNewValue), $(vStartDateTime), $(vEndDateTime)];
DROP Table CPDocHistTemp;
ENDIF;
NEXT DocStatusCountCounter;
ENDIF;
NEXT DocStatusDocIDCounter;
DROP Table DistDocStatusDocIDCount;
DROP Table DistNextStepDocIDCount;