Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following scenario: I have a Database table that looks like the following:
Order# | OrderStatus | ApprovedBy | DateOfApproval/Rejection |
---|---|---|---|
8 | Approved | Mary | 09-29-15 |
8 | Approved | Paul | 09-30-15 |
6 | Approved | Mary | 09-28-15 |
6 | Rejected | Mark | 09-30-15 |
4 | Approved | Paul | 09-25-15 |
4 | Unapproved | - | - |
2 | Unapproved | - | - |
2 | Unapproved | - | - |
Each order have to be approved by two persons and the OrderStatus can be Approved, Rejected and Unapproved.
So basically I want a script statement that retrieve ONE record for each Order that have been approved by both persons only. Furthermore, I also need to know the latest DateOfApproval/Rejection of the Oder. Therefore in the case above I want the following record:
Order# | OrderStatus | Final Approval Date |
---|---|---|
8 | Approved | 09-30-15 |
Although I managed to do this in SQL using GROUP BY and HAVING clauses, I can't find a way how this can be done in Qlikview.
Can anyone please shed some light how this can be dealt with?
hi
no problem
tweek your script to this one
Load *
where OrderStatus='Approved';
load Order#,
only(OrderStatus) AS OrderStatus,
max(Date) AS DateofApproval
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet1)
Group By Order#;
basically it'll have values in orderstatus only if all records have the same status
hi
this script should work for you
Load *
where Counter=2;
load Order#,
maxstring(OrderStatus) AS OrderStatus,
count(distinct ApprovedBy) as Counter,
max(Date) AS DateofApproval
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet1)
where OrderStatus='Approved'
Group By Order#;
drop field Counter
Here you are.
Cheers,
Patric
Thanks for your quick replay!
Unfortunately this is still retrieving both records when I create a table with the fields {Order#, OrderStatus, FinalApprovalDate}. In reality (although i didn't mention it) the QVD from where I am retrieving data also have to other fields namely, ApprovalLevel and ApprovalTime. Is this making a difference?
Another thing that I didn't mentioned is there are cases where an Order need only to be approved by one person. i.e. it will consists of one record in the QVD. Does this effect the script?
So in simple terms, the QVD consists of:
- 2 records for each Order that required to be approved by two persons
- 1 records for each Order that required to be approved by one person
I'm sorry but although I have SQL experience, I am quite new to QV!
Liron, thanks for your feedback! This work fine!! However since I thought that it does not make a difference, I didn't mention in the previous post that there are cases where an Order need only to be approved by one person. i.e. it will consists of one record in the QVD.
Thus besides the example above it is also possible to have:
Order# | OrderStatus | ApprovedBy | DateOfApproval/Rejection |
---|---|---|---|
11 | Approved | John | 10-08-15 |
10 | Rejected | John | 07-09-14 |
9 | Unapproved | Mike | 01-01-14 |
Thus I would require the following result:
Order# | OrderStatus | Final Approval Date |
---|---|---|
8 | Approved | 09-30-15 |
11 | Approved | 10-08-15 |
Does this new requirement require a complete change in the script?
Thanks again,
Matt
hi
no problem
tweek your script to this one
Load *
where OrderStatus='Approved';
load Order#,
only(OrderStatus) AS OrderStatus,
max(Date) AS DateofApproval
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet1)
Group By Order#;
basically it'll have values in orderstatus only if all records have the same status
Perfect Liron! Thanks for your brilliant feedback!