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: 
Not applicable

Translate Having Statement to qlikview

Hello all,

I need to translate this SQL statement:

SELECT Customerid ,

               Sum(amount)

From Orders

group by Customerid

having max(orderdate)

thanks

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

No. The first step is the SELECT statement. It produces three fields: Customerid, Amount, and MaxOrderDate. The second step is the preceding load. It can only "see" these three fields, so the field OrderDate is not available.

But you can do what I suggested in my previous answer: Use MaxOrderDate in a comparison.

See also Preceding Load

HIC

View solution in original post

5 Replies
maxgro
MVP
MVP

I think you miss the bold part in the sql statement

SELECT Customerid ,

               Sum(amount)

From Orders

group by Customerid

having max(orderdate)   > something

there is no need to translate the statement in Qlik, usually you can also use your sql in QlikView script

SQL

SELECT Customerid ,

               Sum(amount)

From Orders

group by Customerid

having max(orderdate)   > something;



Not applicable
Author

thak you very much

hic
Former Employee
Former Employee

Just as maxgro‌ says, you can send the entire SELECT statement as-is to the ODBC driver. This is probably the best solution.

However, an alternative method is to use a preceding load:

Load * Where MaxOrderDate > something;

SQL SELECT

     Customerid,

     Sum(amount) as Amount,

     Max(orderdate) as MaxOrderDate

From Orders

group by Customerid ;

HIC

Not applicable
Author

can i do that?

Load * Where OrderDate = max(OrderDate);

SQL SELECT

     Customerid,

     Sum(amount) as Amount,

     Max(orderdate) as MaxOrderDate

From Orders

group by Customerid ;

thank's

hic
Former Employee
Former Employee

No. The first step is the SELECT statement. It produces three fields: Customerid, Amount, and MaxOrderDate. The second step is the preceding load. It can only "see" these three fields, so the field OrderDate is not available.

But you can do what I suggested in my previous answer: Use MaxOrderDate in a comparison.

See also Preceding Load

HIC