Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gopal5879
Creator
Creator

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

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

14 Replies
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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?

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Assume workitem can be used as the key,

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

clipboard_image_0.png

Refer qvw attached for reference.

Thanks and regards,

Arthur Fong

gopal5879
Creator
Creator
Author

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

I may loose one record.

 

Maintenance StartMaintenance1/5/2018
 Maintenance1/6/2018
Maintenance EndMaintenance1/7/2018
Maintenance StartMaintenance1/8/2018
 Maintenance1/9/2018
 Maintenance1/10/2018
 Maintenance1/11/2018
Maintenance EndMaintenance1/12/2018

 

I need output as below, 2 separate records

Maintenance1/5/20181/7/2018
Maintenance1/8/20181/12/2018
gopal5879
Creator
Creator
Author

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_statusstatusdateworkitemworkitemseqkey
Maintenance StartMaintenance1/5/201820704002019072222502
 Maintenance1/6/201820704002019072222624
Maintenance EndMaintenance1/7/201820704002019072222503
Clarification StartClarification1/8/201820704002019072222498
 Clarification1/9/201820704002019072222500
Clarification EndClarification1/10/201820704002019072222501
Maintenance StartMaintenance1/10/201820704002019072222505
 Maintenance1/11/201820704002019090936640
Maintenance EndMaintenance1/12/201820704002019072222508
     
     
Need Output as below    
StatusStartDateEndDate  
Maintenance1/5/20181/7/2018  
Clarification1/8/20181/10/2018  
Maintenance1/10/20181/12/2018  
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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

JUCEDAMA
Contributor
Contributor

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

gopal5879
Creator
Creator
Author

I do have timestamp

gopal5879
Creator
Creator
Author

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