Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
;
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
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