Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

BootCam
Contributor

How to get minimum date in the granular level

Hi All,

I have a problem Like this:

I have a date field which has all the update date and I have to take the Minimum Date from this field in the granular level.

And i have a status field that has all the Status Code.

Also another field "Processed" that has Boolean value T and F

Now i have to make a field "Active date" in the script based on these three fields.

Logic i am trying use is,

IF(Match([Status],'A','P') and [Processed]='F' and [Update Date]=Min([Update Date]), [Update Date], ' ') as [Active Date]

Seems like my logic is set on the Minimum Date from the Update date not the minimum date from the item/granular level for every products.

How do i write down the code in the script that can work in the Item level? Meaning, getting the minimum date for every product.

Thanks,

Sk

1 Solution

Accepted Solutions
MVP
MVP

Re: How to get minimum date in the granular level

You can add to your table a min update date, using a group by and left join (bold)

yourtable:

load

     product,

     status,

     processed,

     updatedate

.......

left join (yourtable)

load

     product,

     min(updatedate) as minupdatedate

resident yourtable

group by product;          // I think you want the min date by product

then you can do a resident table with an if condition on status, processed, updatedate, minupdatedate to calculate the activedate field

3 Replies

Re: How to get minimum date in the granular level

Perhaps like this:

SummaryTable:

LOAD

     Product,

     Min([Update Date]) as [Active Date]

FROM

     ...source...

WHERE

     Match(Status, 'A','P') and Processed = 'F'

GROUP BY

     Product

     ;


talk is cheap, supply exceeds demand
BootCam
Contributor

Re: How to get minimum date in the granular level

Hi Gysbert,

With the Where clause, Am i not limiting my table with the Match(Status, 'A','P') and Processed = 'F' filter?

But my goal is to keep all the data and make a new filed Active date.


Something like that

IF(Match([Status],'A','P') and [Processed]='F' and [Update Date]=Min([Update Date]), [Update Date], ' ') as [Active Date]

I know this piece of code is wrong but the idea is like that.


Thanks,

Sk

MVP
MVP

Re: How to get minimum date in the granular level

You can add to your table a min update date, using a group by and left join (bold)

yourtable:

load

     product,

     status,

     processed,

     updatedate

.......

left join (yourtable)

load

     product,

     min(updatedate) as minupdatedate

resident yourtable

group by product;          // I think you want the min date by product

then you can do a resident table with an if condition on status, processed, updatedate, minupdatedate to calculate the activedate field

Community Browser