GROUP BY and HAVING clauses in Qlikview

[#1]

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?

[lironbaram]

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

[pamaxeed]

Here you are.

Cheers,

Patric

[#4]

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!

[#5]

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

[lironbaram · Accepted solution]

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

[#7]

Perfect Liron! Thanks for your brilliant feedback!