Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor III

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
Highlighted
Partner
Partner

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

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?

Highlighted
Partner
Partner

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

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

Highlighted
New Contributor III

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

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
Highlighted
New Contributor III

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

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  
Highlighted
Partner
Partner

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

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

Highlighted
Partner
Partner

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

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

Highlighted
New Contributor

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

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

Highlighted
New Contributor III

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

I do have timestamp

New Contributor III

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

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