topic Re: load only min data value row in QlikView

load only min data value row

andyrebs — Tue, 24 Jan 2023 16:04:42 GMT

I have the following script:

Tabella1°Qtà:
SQL select
max(POQ.ITMREF_0) as [Article],
max(POQ.QTYSTU_0) as [Qty 1°arr.],
min(POQ.EXTRCPDAT_0) as [1° Receiving Date ],
max(POQ.RCPQTYSTU_0) as [1° Qty Ric. For.]
//max(POQ.YDATARCVPR_0) as [1° Date Receiving Conf.]
from xxxx.yyyy.PORDERQ POQ
where POQ.QTYSTU_0-POQ.RCPQTYSTU_0 >0 and POQ.RCPCLEFLG_0 = 1 and year(POQ.YDATARCVPR_0)<>2000
group by POQ.ITMREF_0

I would load only 1 row per POQ.ITMREF_0 and only the data of the row with the min date POQ.EXTRCPDAT_0

How can I set the script?

Many thanks

Andrea

Re: load only min data value row

jcmachado — Wed, 25 Jan 2023 20:56:33 GMT

You can modify your query to include a subquery that selects the minimum date for each item, and then use that in the main query to filter the rows. Here's an example of how you can modify your query:

SQL select
max(POQ.ITMREF_0) as [Article],
max(POQ.QTYSTU_0) as [Qty 1°arr.],
min(POQ.EXTRCPDAT_0) as [1° Receiving Date ],
max(POQ.RCPQTYSTU_0) as [1° Qty Ric. For.]
//max(POQ.YDATARCVPR_0) as [1° Date Receiving Conf.]
from xxxx.yyyy.PORDERQ POQ
INNER JOIN (SELECT ITMREF_0, min(EXTRCPDAT_0) as min_date from xxxx.yyyy.PORDERQ where POQ.QTYSTU_0-POQ.RCPQTYSTU_0 >0 and POQ.RCPCLEFLG_0 = 1 and year(POQ.YDATARCVPR_0)<>2000 group by ITMREF_0) subquery
ON POQ.ITMREF_0 = subquery.ITMREF_0 AND POQ.EXTRCPDAT_0 = subquery.min_date
where POQ.QTYSTU_0-POQ.RCPQTYSTU_0 >0 and POQ.RCPCLEFLG_0 = 1 and year(POQ.YDATARCVPR_0)<>2000
group by POQ.ITMREF_0

The subquery selects the minimum date for each item and join it with the main query, filtering only the rows with the min date of each item.

Please note that the query might require some adjustments depending on the specific structure and contents of the xxxx.yyyy.PORDERQ table.

Re: load only min data value row

andyrebs — Thu, 26 Jan 2023 17:48:18 GMT

Hi jcmachado,

many thanks for your reply.

I tested your subquery, it seems working!

Best regards

Andrea