Type based on the date calculation

[apthansh]

Hi -  I have data as below.

Date                             Type

1/2/2020                   Main

3/2/2020                   Sub

5/4/2020                   Main

My logic is to look for date that is closer today, but < today.So, I have as below..

max(if(Date < today(),Date))

Now I need to add the Type for this date as well...so it should be, 3/2/2020 - Sub.

Thank you much.

[apthansh]

@Vegar , any thoughts on this ?

[HirisH_V7]

Hi you can use this,

Temp:
LOAD *
,Date(Date#(Date,'MM/DD/YYYY'),'DD-MM-YYYY') as Date_Create
 INLINE [
    Date             
    1/2/2020                      
    3/2/2020                   
    5/4/2020                   
];

Temp_Max:
Load Max(Temp) as Sub;
Load 
Date(if(Date_Create < today(),Max(Date_Create) )) as Temp
Resident Temp Group by Date_Create;

Let vMaxSub=Peek('Sub');

Data:
Load Date_Create,
If(Num(Date_Create)=$(vMaxSub),'Sub','Main') as Type
Resident Temp;

DROP Table Temp,Temp_Max;

[Vegar]

Did @HirisH_V7  respons solve your issue?

[apthansh]

@Vegar  - Thank you.. I was able to get that thru the below expression.

FirstSortedValue(Name, -if(Date<Today(),Date))

but now I want to Cinactenate Name as I have same date with Different Names...I tried below,..no luck

FirstSortedValue(conact(Name,'-'), -if(Date<Today(),Date))

[shivanisapkale]

Hi,

Create a key at back end for name and date and use that.

Regards,

Shivani Sapkale