topic Re: SQL query in QlikView

SQL query

Thu, 18 Dec 2014 14:08:38 GMT

Hi All,

I have a set of data

ID| Amount| Date | Status

1|xxx1|dd-MM-YYY|Confirmed

1|xxx2|dd-MM-YYY|Pending

1|xxx3|dd-MM-YYY|Pending

2|xxx4|dd-MM-YYY|Confirmed

2|xxx4|dd-MM-YYY|Pending

2|xxx4|dd-MM-YYY|Pending...

I want the records of the top 2 (i.e confirmed & 1st pending )in each ID is it possible in SQL query. Query experts please help.

Thanks in advance,

Sai.

Re: SQL query

Anonymous — Thu, 18 Dec 2014 14:14:58 GMT

Hi,

Use below code in script:

Table:
LOAD *,
Status as PendingStatus
From ....
where Status = 'Pending';

Concatenate

LOAD *,
Status as ConfirmedStatus
From ....
where Status = 'Confirmed';

Regards

Neetha

Re: SQL query

anbu1984 — Thu, 18 Dec 2014 14:30:27 GMT

Select ID, Amount, Date ,Status From table Where Status = 'Confirmed' Or (ID,Date,Status) in

(Select ID,Max(Date) As Date,Status From table where Status = 'Pending' Group by ID,Status)

Re: SQL query

Peter_Cammaert — Thu, 18 Dec 2014 16:10:58 GMT

Hmmm.

"top" seems to refer to the position on the screen, as there is no indication that either Amount or Date play a part in qualifying the top "Pending" record.

To accomplish this, you must pick the "Pending" record with the lowest record number. Numbering records is highly dependent on your database system. So it would be nice if you could tell us the type of database you're using.

Peter

Re: SQL query

Fri, 19 Dec 2014 02:42:44 GMT

Date and amount has not impact on this. DB used is SQL.

Re: SQL query

jagan — Fri, 19 Dec 2014 02:57:23 GMT

Hi,

Check this query.

SELECT *

FROM (

SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC) AS RecordNum,

ID, Amount, Date, Status

FROM TableName) AS Temp

WHERE RecordNum <= 2

Regards,

Jagan.