Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, please see my table below. I want to create a variable under these conditions: H_DAYS must = 1, M_DAYS must be between 1 and 90. And if these parameters are true I want the variable to return 1, or count only 1 row, whichever is easier. So ID 3256 meets all the conditions, but I don't want a count of 3, I want a count of 1, or a variable that returns the number 1. Please help!
ID | D_DATE | DI_DATE | H_DAYS | M_DAYS |
3256 | 7/2/2018 | 6/29/2018 | 1 | 4 |
3256 | 7/2/2018 | 6/29/2018 | 1 | 4 |
3256 | 7/2/2018 | 6/29/2018 | 1 | 4 |
7789 | 6/29/2018 | 3/15/2018 | 1 | 106 |
7789 | 6/29/2018 | 3/15/2018 | 1 | 106 |
7789 | 6/29/2018 | 3/15/2018 | 1 | 106 |
Maybe I am thinking too much, but do you want to count 1 is the ID ever has records matching that criteria?
ID | D_DATE | DI_DATE | H_DAYS | M_DAYS |
3256 | 7/2/2018 | 6/29/2018 | 1 | 4 |
3256 | 7/2/2018 | 6/29/2018 | 1 | 91 |
3256 | 7/2/2018 | 6/29/2018 | 1 | 4 |
Like would this situation be 0 because it has a record that doesn't meet the criteria, or would it still be one? Or does this not happen?
Maybe: count({< H_DAYS = {1}, M_DAYS = {">=1<=90"}>} distinct ID)
- Marcus
this actually does happen, I just encountered it! I would like to select the MIN for M_DAYS for each specific ID to base the formula on. would you be able to help?
hi marcus, what if M_DAYS has different values for the same ID, how can I write the formula so that it is basing the calculation off of the minimum value for that specific ID?
I think it's depending on your datamodel but you could try:
count({< H_DAYS = {1}, M_DAYS = {"=min(M_DAYS)>=1 and min(M_DAYS)<=90"}>} distinct ID)
An alternatively might be to use a flag within the script maybe with something like:
t: load ID, min(M_DAYS) as X from Source where H_DAYS = 1 group by ID;
M: mapping load ID, 1 from resident t where X >=1 and X <=90;
T: load *, applymap('M', ID, 0) as Flag from Source;
- Marcus