Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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
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 |
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 |
Add in workitemseqkey into the groupby statement. Should work if that is the key to identify each transaction.
How do you identify different windows of time? You have timestamp records in your table?
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
I do have timestamp
If I add workitemseqkey , then it would return min date and max date for each of the records