Solved: Arranging table data to formulate start date and e... - Qlik Community

vvira1316 · ‎2016-12-05

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

vvira1316 · ‎2016-12-06

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;

View solution in original post

sinanozdemir · ‎2016-12-05

Hi,

Would you happen to have sample data to share here?

Thanks

vvira1316 · ‎2016-12-05

Hi,

It is the same data that I had included in the question but I'm including the excel file of it.

Vijay

sinanozdemir · ‎2016-12-05

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

vvira1316 · ‎2016-12-05

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

Report Inappropriate Content · ‎2016-12-05

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

vvira1316 · ‎2016-12-06

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;

Arranging table data to formulate start date and end date from different records of same table