topic Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in in QlikView

QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in JPG

gopal5879 — Sat, 16 Nov 2024 20:04:33 GMT

Hello folks,

I have sample input data as below

Need output as show in screen shot attached jpg file . I need to do this using QlikView script , any help is highly appreciated as I am running under tight deadline of project

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

Arthur_Fong — Tue, 17 Sep 2019 00:27:18 GMT

What is the key to identify Maintenance green and Maintenance blue?

They have the same workitem but why they are categorized in different startdate and endDate?

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

Arthur_Fong — Tue, 17 Sep 2019 00:40:58 GMT

Assume workitem can be used as the key,

apply min(date) and max(date) aggregation group by status and workitem:

Refer qvw attached for reference.

Thanks and regards,

Arthur Fong

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

gopal5879 — Tue, 17 Sep 2019 10:19:48 GMT

Thanks Aurthur for Reply, Problem If I do Min and Max with group by WorkItem, Status

I may loose one record.

Maintenance Start	Maintenance	1/5/2018
	Maintenance	1/6/2018
Maintenance End	Maintenance	1/7/2018
Maintenance Start	Maintenance	1/8/2018
	Maintenance	1/9/2018
	Maintenance	1/10/2018
	Maintenance	1/11/2018
Maintenance End	Maintenance	1/12/2018

I need output as below, 2 separate records

Maintenance	1/5/2018	1/7/2018
Maintenance	1/8/2018	1/12/2018

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

gopal5879 — Tue, 17 Sep 2019 10:32:54 GMT

The transactions are independent and business wants to see separate keys. WorkItem, WorkItemSeqKey together can be considered as key . Work Item can go maintenance during different window of times and business wants to see as 2 different records

Input Data flow

rec_status	status	date	workitem	workitemseqkey
Maintenance Start	Maintenance	1/5/2018	2070400	2019072222502
	Maintenance	1/6/2018	2070400	2019072222624
Maintenance End	Maintenance	1/7/2018	2070400	2019072222503
Clarification Start	Clarification	1/8/2018	2070400	2019072222498
	Clarification	1/9/2018	2070400	2019072222500
Clarification End	Clarification	1/10/2018	2070400	2019072222501
Maintenance Start	Maintenance	1/10/2018	2070400	2019072222505
	Maintenance	1/11/2018	2070400	2019090936640
Maintenance End	Maintenance	1/12/2018	2070400	2019072222508


Need Output as below
Status	StartDate	EndDate
Maintenance	1/5/2018	1/7/2018
Clarification	1/8/2018	1/10/2018
Maintenance	1/10/2018	1/12/2018

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

Arthur_Fong — Tue, 17 Sep 2019 11:12:24 GMT

Add in workitemseqkey into the groupby statement. Should work if that is the key to identify each transaction.

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

Arthur_Fong — Tue, 17 Sep 2019 11:14:37 GMT

How do you identify different windows of time? You have timestamp records in your table?

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

JUCEDAMA — Tue, 17 Sep 2019 12:58:51 GMT

Assuming the input is always in the right order, I would try:

1. removing empty rec_status rows

2. If rec_status is *end, peek(date,-1) as start, date as end

3. remove *start rows

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

gopal5879 — Tue, 17 Sep 2019 14:52:57 GMT

I do have timestamp

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

gopal5879 — Tue, 17 Sep 2019 14:54:24 GMT

If I add workitemseqkey , then it would return min date and max date for each of the records

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

Arthur_Fong — Tue, 17 Sep 2019 14:57:24 GMT

Replace the seqkey with timestamp and see if it works

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

StarinieriG — Tue, 17 Sep 2019 15:26:13 GMT

try to do something like this:

NoConcatenate
START:
LOAD *,
RowNo() as ID,
If(Previous(status)=status,status & RowNo(),status) as Dimension;
LOAD
status,
date as start
Resident TABLE
Where Index(rec_status,'start')>0
Order By status,date
;

Left Join
LOAD
RowNo() as ID,
If(Previous(status)=status,status & RowNo(),status) as Dimension,
end
;
LOAD
status,
date as end
Resident TABLE
Where Index(rec_status,'end')>0
Order By status,date
;

DROP Table TABLE;

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

gopal5879 — Mon, 23 Sep 2019 14:39:11 GMT

Thanks a lot, I will try

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

Brett_Bleess — Thu, 26 Sep 2019 20:23:20 GMT

Gopal, just wanted to see if you got things figured out here? If so, be sure to use the Accept as Solution button on any of the posts from folks that helped you get things working. If you did something different, consider posting that and then mark that as the solution so others will know what worked. If you still need help, drop an update on where things stand.

Regards,
Brett

Re: QLIKVIEW script - Need help in transforming data , based on Sample work data Need output as in

qliksus — Fri, 27 Sep 2019 04:58:40 GMT

Create a group based on the each start and end and do the max and min based on that group

clar:
LOAD * INLINE [
F1, F2, F3
Clarification Start, Clarification, 1/1/2018
, Clarification, 1/4/2018
Clarification End, Clarification, 1/2/2018

Maintenance Start, Maintenance, 1/5/2018
, Maintenance, 1/6/2018
Maintenance End, Maintenance, 1/7/2018
Maintenance Start, Maintenance, 1/8/2018
, Maintenance, 1/9/2018
, Maintenance, 1/10/2018
, Maintenance, 1/11/2018
Maintenance End, Maintenance, 1/12/2018
];

load

* ,

if( RowNo()=1 ,1 ,
if( wildmatch(F1,'*Start*') or IsNull(Peek(F1)) , peek(row)+1, peek(row) )) as row

Resident clar ;

DROP Table clar;

Calulate Min/Max based on row and F2 field