Dates based on other fields

[Sai33]

Hi all,

I've my source data in the following format

ID	Date	Loc
A123	01.10.2020	ABC
B245	03.10.2020	ABC
A123	11.10.2020	ABC
A123	12.10.2020	456
A123	01.11.2020	FGT
A123	10.11.2020	789
A123	15.11.2020	DEF

I would like to generate the following dates for each ID in each LOC based on the LOC value. My expected data would be as follows:

ID	Valid From Date	Valid To date
A123	01.10.2020	11.10.2020
B245	03.10.2020	Today
A123	01.11.2020	09.11.2020
A123	15.11.2020	Today

Basically, a ID has to have Valid from and To dates until the LOC is not an Numeric value, if there is no new record for a ID then Today() has to be considered as the Vaild To Date.

Please, let me know if i'm not clear enough.

Regards

Sai 

[Anonymous]

IF(LOC='ABC', Aggr(max(Date),ID),

      If(LOC='ABC' and Aggr(max(Date),ID)=Date, Today()) AS Valid To date.

In script use Group by for ID

[PrashantSangle]

Hi,

Try below code

Temp:
Load ID,Date(Date#(Date,'DD.MM.YYYY')) as Date,Loc inline [
ID, Date, Loc
A123, 01.10.2020, ABC
B245, 03.10.2020, ABC
A123, 11.10.2020, ABC
A123, 12.10.2020, 456
A123, 01.11.2020, FGT
A123, 10.11.2020, 789
A123, 15.11.2020, DEF
];

NoConcatenate
Temp1:
Load ID,
max(Date) as Valid_To_Date,
Loc
Resident Temp
Group by ID, Loc
;

Left join
Load ID,
min(Date) as Valid_From_Date,
Loc
Resident Temp
Group by ID, Loc
;

NoConcatenate
Final:
Load ID,
Date(Valid_From_Date) as Valid_From_Date,
if(Valid_From_Date = Valid_To_Date,'Today',Date(Valid_To_Date)) as Valid_To_Date,
Loc
Resident Temp1;

Drop Tables Temp, Temp1; 

Regards,

Prashant Sangle

[MayilVahanan]

Hi @Sai33 

May be Try like this

Res:
LOAD *, RowNo() as Rec INLINE [
ID, Date, Loc
A123, 01.10.2020, ABC
B245, 03.10.2020, ABC
A123, 11.10.2020, ABC
A123, 12.10.2020, 456
A123, 01.11.2020, FGT
A123, 10.11.2020, 789
A123, 15.11.2020, DEF
];

Final:
Load *, If(ID <> Previous(ID), Date(TOday()),If(ID = Previous(ID) and Previous(LocFlagNum)=1, Date(Peek(Date1)-1), if(ID=Previous(ID) and LocFlagNum = 0, Peek(Date1), Date))) as Date1;
Load *, if(IsNum(Loc), 1,0) as LocFlagNum, ID&'|'&Loc as Key Resident Res order by ID, Rec desc, Loc;

ValidDate:
Load Key, Date(Min(Date)) as ValidFromDate, Date(Max(Date1)) as ValidToDate Resident Final where LocFlagNum = 0 Group by Key;

DROP Table Res;

EXIT SCRIPT;

Output:

Highlighted record will update based on Today Date.