Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to do date calculations.
How can I calculate the difference in the data array of data B from the minimum value of data A?
Hi
Try like below
Temp:
Load * from urtableB;
Left Join
Load Min(DataA) as MinDate resident tableA;
FinalDataB:
Load *, DataB - MinDate as DataDiff resident Temp;
Drop table Temp;
@louise119 Could you please provide some sample data and also the expected output?
@sidhiq91 wrote:
@louise119 Could you please provide some sample data and also the expected output?
For data A and data B below,
I'd like to take the difference of Data B from the minimum date of Data A.
The calculation formula is as follows.
2019/12/09 - 2019/01/01 = ??
12/12/2020 - 01/01/2019 = ??
2021/01/18 - 2019/01/01 = ??
2021/05/29 - 2019/01/01 = ??
2021/09/23 - 2019/01/01 = ??
2022/03/02 - 2019/01/01 = ??
2022/04/05 - 2019/01/01 = ??
-------------------------------------------------- ----------
Data A (min: 2019/01/01 in this case)
2019/01/01
2020/12/03
2020/12/31
2021/05/28
Data B
2019/12/09
2020/12/12
2021/01/18
2021/05/29
2021/09/23
2022/03/02
2022/04/05
Hi
Try like below
Temp:
Load * from urtableB;
Left Join
Load Min(DataA) as MinDate resident tableA;
FinalDataB:
Load *, DataB - MinDate as DataDiff resident Temp;
Drop table Temp;
Try this
DataA:
Load *Inline [
Date1
2019/01/01
2020/12/03
2020/12/31
2021/05/28
];
tempDataA:
Load Min(Date1) as Date_min
Resident DataA;
Let vmin = num(Peek('Date_min',0,'tempDataA'));
drop table tempDataA;
DataB:
Load * Inline [
Date2
2019/12/09
2020/12/12
2021/01/18
2021/05/29
2021/09/23
2022/03/02
2022/04/05
];
tempDataB:
Load
Date2,
Date($(vmin)),
num(Date2),
$(vmin),
num(Date2)-$(vmin),
Date(num(Date2)-$(vmin))
Resident DataB;
Drop tables DataA,DataB;
Exit Script;