Date calculation

[louise119]

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?

[sidhiq91]

@louise119  Could you please provide some sample data and also the expected output?

[louise119]

---

@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

[MayilVahanan · Accepted solution]

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;

[Bhuvi]

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;